Simple query seems to be ignoring date index

[Greg Schubin]

I have a relatively simple table with a simple query that is taking a long time. I am using version 5.5.32-0ubuntu0.13.04.1 (It's actually Linux Mint Olivia.)

I created a test table to demonstrate:

mysql> show create table test;

| test | CREATE TABLE `test` (
`sym` char(5) COLLATE utf8_bin DEFAULT NULL,
`date` date DEFAULT NULL,
`vol` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

I then filled it with 11 million rows from the other table. If I add an index on 'sym' a simple

'SELECT * FROM test WHERE Sym='ORCL';

shows

6467 rows in set (0.10 sec)

I then add an index on date and it takes much longer for something like:

SELECT * FROM test WHERE Date='2008-10-1';

2488 rows in set (12.40 sec)

This is susp[iciously close to the time without the index, and the hard drive light is on for the duration of the query.

I have tried USE INDEX and even FORCE INDEX but it seems to have no effect.

BTW SELECT COUNT(*) FROM test WHERE Date='2008-10-1';

returns

+----------+
| COUNT(*) |
+----------+
| 2488 |
+----------+
1 row in set (0.02 sec)

EXPLAIN SELECT * FROM test WHERE Date='2008-10-1';

shows (I called the Date index 'dt')

+------+-------------+---------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | test | ref | dt | dt | 4 | const | 2445 | |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+

Can anyone tell me why this takes so long? Let me know if i can provide more information. (Note there is a small diference in the number of rows in the query and the EXPLAIN 2488 vs 2445.)