MySQL Forums :: Performance :: Simple query seems to be ignoring date index


Advanced Search

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 1573 Greg Schubin 10/26/2013 11:38AM
Re: Simple query seems to be ignoring date index 565 Greg Schubin 10/28/2013 08:56AM
Re: Simple query seems to be ignoring date index 519 Rick James 10/28/2013 09:35PM
Re: Simple query seems to be ignoring date index 564 Greg Schubin 10/29/2013 10:19AM
Re: Simple query seems to be ignoring date index 611 Rick James 10/30/2013 07:47PM
Re: Simple query seems to be ignoring date index 601 Greg Schubin 10/31/2013 12:23AM
Re: Simple query seems to be ignoring date index 549 Øystein Grøvlen 11/01/2013 02:47AM
Re: Simple query seems to be ignoring date index 589 Greg Schubin 11/01/2013 11:20AM
Re: Simple query seems to be ignoring date index 758 Rick James 11/01/2013 10:45PM
Re: Simple query seems to be ignoring date index 581 Greg Schubin 11/02/2013 09:45PM
Re: Simple query seems to be ignoring date index 540 Rick James 11/02/2013 09:59PM
Re: Simple query seems to be ignoring date index 588 Øystein Grøvlen 11/04/2013 04:55AM


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.