I am using 5.5.32. The original table is stock prices by stock symbol and date. It is populated by first creating a CSV file then LOADing it into the table. The lines in the CSV file are like:
A,2013-10-17,3548400,52.65,51.25,1.02,1.02,1.02,1.01,1.09
A,2013-10-16,2450800,51.56,51.25,1.01,1.01,0.99,1.01,1.06
A,2013-10-15,1961900,50.88,51.22,1.02,0.99,0.98,1.03,1.04
So they are ordered first by symbol then by descending date. I built the test table simply by doing an INSERT from the real table. I have no idea how the data would be arranged on the disk. I could try doing the INSERT ... ORDER BY date to see if that makes any difference.
I still find it suspect that the EXPLAIN leaves the Extra field blank, even with USE INDEX and FORCE INDEX. Here is the EXPLAIN for FORCE INDEX:
mysql> explain select * from test force index (dt) where mdate=20121022;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | test | ref | dt | dt | 3 | const | 2876 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
EDIT: That worked! I redid the INSERT with ORDER BY date and now the SELECT on date comes back in 0.08 seconds and the SELECT on sym takes over 6 seconds.
Is there any way to do ORDER BY date on the LOAD FILE? I can only think of LOADing it into a temporary table then doing an INSERT with ORDER BY.
Edited 1 time(s). Last edit at 11/01/2013 11:38AM by Greg Schubin.