MySQL index on date fields question

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

LVL 15
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
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.
dirknibleckAuthor Commented:
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.
nemws1Database AdministratorCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.