MySQL datetime index is not working

Question

Table structure:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| total       | int(11)  | YES  |     | NULL    |                |
| thedatetime | datetime | YES  | MUL | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

Total rows: 137967

mysql> explain select * from out where thedatetime <= NOW();
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | out         | ALL  | thedatetime   | NULL | NULL    | NULL | 137967 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+

The real query is much more longer with more table joins, the point is, I can't get the table to use the datetime index. This is going to be hard for me if I want to select all data until certain date. However, I noticed that I can get MySQL to use the index if I select a smaller subset of data.

mysql> explain select * from out where thedatetime <= '2008-01-01';
+----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+
|  1 | SIMPLE      | out         | range | thedatetime   | thedatetime | 9       | NULL | 15826 | Using where |
+----+-------------+-------------+-------+---------------+-------------+---------+------+-------+-------------+

mysql> select count(*) from out where thedatetime <= '2008-01-01';
+----------+
| count(*) |
+----------+
|    15990 |
+----------+

So, what can I do to make sure MySQL will use the index no matter what date that I put?

Answer 1

There are two things in play here -

1. Index is not selective enough - if the index covers more than approx. 30% of the rows, MySQL will decide a full table scan is more efficient. When you contract the range the index kicks in.

2. One index per table in a join

The real query is much more longer with more table joins, the point is ...

The point is exactly because it has joins that it probably can't use that index. MySQL can use one index per table in a join (unless it qualifies for an index-merge optimization). If the primary key is already used for the join, thedatetime won't be used. In order to use it, you need to create a multi-column index on the join key + thedatetime index, in the correct order.

Check the EXPLAIN of the actual query to see which key MySQL uses for the join. Modify that index to include the thedatetime column as well, or create a new multi-column index from both (depending on what you use the join key for).

Answer 2

Everything works as it is supposed to. :)

Indexes are there to speed up retrieval. They do it using index lookups.

In you first query the index is not used because you are retrieving ALL rows, and in this case using index is slower (lookup index, get row, lookup index, get row... x number of rows is slower then get all rows == table scan)

In the second query you are retrieving only a portion of the data and in this case table scan is much slower.

The job of the optimizer is to use statistics that RDBMS keeps on the index to determine the best plan. In first case index was considered, but planner (correctly) threw it away.

EDIT
You might want to read something like this to get some concepts and keywords regarding mysql query planner.