• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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`)
) ENGINE=MyISAM AUTO_INCREMENT=7221 DEFAULT CHARSET=latin1;


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

explain-query.csv
0
dirknibleck
Asked:
dirknibleck
  • 2
1 Solution
 
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.
0
 
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.
0
 
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:
EXPLAIN EXTENDED SELECT *
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).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now