We help IT Professionals succeed at work.

MySQL index on date fields question

dirknibleck asked
I'd like to know if I can improve what I've done so that it works, or if MySQL just doesn't operate on date indexes in the way I understood.

I have a table of thousands of records which I need to filter based on a date range (i.e. pull every record between two dates). I've created the table, and I have an index on the date field. However, when I run the query below against the table, it does not seem to be using the index.

Below is the Table structure and the query. Attached is the result of the Explain.
CREATE TABLE `games_copy` (
  `table_id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`table_id`),
  KEY `start_date_2` (`start_date`,`end_date`),
  KEY `start_date` (`start_date`)

SELECT * FROM games_copy WHERE start_date >= '2011-09-01' AND start_date <= '2012-08-31'

explain SELECT * FROM games_copy WHERE start_date >= '2011-09-01' AND start_date <= '2012-08-31'

Open in new window

Watch Question

Nem SchlechtIT Supervisor
Top Expert 2009

I'm a little confused... your EXPLAIN query states that it'll use the 'start_date_2' and 'start_date' indexes.

I generated 100K rows of random dates in 2011 and 2012, inserted them into your same table, ran your query, and it returned almost 40K rows of results in .2 seconds on my machine.

Have you done a lot of DELETEs on this table?  If so, it might be a little fragmented, so running "ANALYZE TABLE games_copy" will help it a little bit, but otherwise everything looks good here.


Am I misreading the Explain output? start_date_2 and start_date are listed under possible keys, however there is nothing listed in the Key column. My understanding was that this meant it found two keys available, but it didn't use any of them.
IT Supervisor
Top Expert 2009
That's because you're selecting on a range.  MySQL *thinks* it'll be able to use those keys, but isn't sure that your search parameters will match any rows within the index until it actually performs the query (in which case it *will* use those indexes).

If you give a more definite query, you'll get a more definite answer.  For example, this EXPLAIN on the start_date being equal to a certain value:
FROM games_copy
WHERE start_date = '2011-09-01'

Open in new window

Will return a 'key' value that shows it definitely will use an index and even has a fairly close guess to the number of rows returns (guessed 167, actual is 177 using my test table):
| id | select_type | table      | type | possible_keys           | key          | key_len | ref   | rows | filtered | Extra       |
|  1 | SIMPLE      | games_copy | ref  | start_date_2,start_date | start_date_2 |         | const |  167 |   100.00 | Using where |

Open in new window

It lists start_date_2 as the key it'll use, as that's the first index that contains the start_date field.  Usually I would switch your two indexes around in my CREATE TABLE statement (put single field indexes before multi-field indexes).