MySQL Forums
Forum List  »  Performance

Simple query seems to be ignoring date index
Posted by: Greg Schubin
Date: October 26, 2013 11:38AM

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.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple query seems to be ignoring date index
1799
October 26, 2013 11:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.