StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSpeed up MySQL query containing 300k+ records
text
Body
copied!<p>I need to lookup all my products (sku's) their latest stock quantity.<br> I have one table (called "stock") with 315k+ records containing this information (a new batch of data is added every day, for most sku's). The reference data is in another table (called "stockfile").</p> <p>This is the query to do it:</p> <pre><code>SELECT s1 . * , f1 . * FROM stock s1 JOIN stockfile f1 ON ( s1.stockfileid = f1.stockfileid ) LEFT OUTER JOIN ( stock s2 JOIN stockfile f2 ON ( s2.stockfileid = f2.stockfileid ) ) ON ( s1.sku = s2.sku AND ( f1.date < f2.date OR f1.date = f2.date AND f1.stockfileid < f2.stockfileid) ) WHERE s2.sku IS NULL </code></pre> <p>These are the table definitions</p> <p><code>SHOW CREATE TABLE</code> stock:</p> <pre><code>CREATE TABLE `stock` ( `stockid` bigint(20) NOT NULL AUTO_INCREMENT, `sku` char(25) NOT NULL, `quantity` int(5) NOT NULL, `creationdate` datetime NOT NULL, `stockfileid` smallint(5) unsigned NOT NULL, `touchdate` datetime NOT NULL, PRIMARY KEY (`stockid`), KEY `stock_sku` (`sku`), KEY `stock_stockfileid` (`stockfileid`) ) ENGINE=MyISAM AUTO_INCREMENT=316039 DEFAULT CHARSET=latin1 </code></pre> <p><code>SHOW CREATE TABLE</code> stockfile:</p> <pre><code>CREATE TABLE `stockfile` ( `stockfileid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `filename` varchar(25) NOT NULL, `creationdate` datetime DEFAULT NULL, `touchdate` datetime DEFAULT NULL, `date` datetime DEFAULT NULL, `begindate` datetime DEFAULT NULL, `enddate` datetime DEFAULT NULL, PRIMARY KEY (`stockfileid`), KEY `stockfile_date` (`date`) ) ENGINE=MyISAM AUTO_INCREMENT=266 DEFAULT CHARSET=latin1 </code></pre> <p>Without any extra indexes it takes... forever. I added these and it sped up to about 250 seconds:</p> <pre><code>CREATE INDEX stock_sku ON stock(sku); CREATE INDEX stock_stockfileid ON stock(stockfileid); CREATE INDEX stockfile_date ON stockfile(date); </code></pre> <p>This is the <code>EXPLAIN</code> on the original query, with these indexes.</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s1 ALL stock_stockfileid NULL NULL NULL 316038 1 SIMPLE f1 eq_ref PRIMARY PRIMARY 2 kompare.s1.stockfileid 1 1 SIMPLE s2 ref stock_sku,stock_stockfileid stock_sku 25 kompare.s1.sku 12 Using where 1 SIMPLE f2 eq_ref PRIMARY,stockfile_date PRIMARY 2 kompare.s2.stockfileid 1 </code></pre> <p>Is there another way to speed things up?</p> <ul> <li>Thanks to Bill Karwin for solving the original query!</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload