database design - 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).