MySQL index in timestamp column that is not used for large date ranges

Question

I have a table like

+-------------------+----------------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+----------------+------+-----+---------------------+-----------------------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | runtime_id | bigint(20) | NO | MUL | NULL | | | place_id | bigint(20) | NO | MUL | NULL | | | amended_timestamp | varchar(50) | YES | | NULL | | | applicable_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | schedule_time | timestamp | NO | MUL | 0000-00-00 00:00:00 | | | quality_indicator | varchar(10) | NO | | NULL | | | flow_rate | decimal(15,10) | NO | | NULL | | +-------------------+----------------+------+-----+---------------------+-----------------------------+ 

I have a scheduled index like

 create index table_index on table(schedule_time asc); 

There are currently 2121552+ entries in the table.

I do not understand when I explain

 explain select runtime_id from table where schedule_time >= now() - INTERVAL 1 DAY; +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+ | 1 | SIMPLE | table | range | table_index | table_index | 4 | NULL | 38088 | Using where | +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+ 1 row in set (0.00 sec) 

The above index is used, but below one.

 mysql> explain select runtime_id from table where schedule_time >= now() - INTERVAL 30 DAY; +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | table | ALL | table_index | NULL | NULL | NULL | 2118107 | Using where | +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) 

I will be very grateful if someone can point out what is wrong here, since the data is updated every 12 minutes, and after 30 days or 60 days will be very slow.

The final query in which I plan to use it is as follows

 select avg(flow_rate),c.group from table a ,(select runtime_id from table where schedule_time >= now() - INTERVAL 1 DAY group by schedule_time ) b,place c where a.runtime_id = b.runtime_id and a.place_id = c.id group by c.group; 

Update =====>

According to the comments between errors too.

 mysql> explain select runtime_id from table where schedule_time between '2013-07-17 12:48:00' and '2013-08-17 12:48:00'; +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | table | ALL | table_index | NULL | NULL | NULL | 2118431 | Using where | +----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> explain select runtime_id from table where schedule_time between '2013-08-16 12:48:00' and '2013-08-17 12:48:00'; +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+ | 1 | SIMPLE | table | range | table_index | table_index | 4 | NULL | 38770 | Using where | +----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+ 1 row in set (0.00 sec) 

Update 2 ========>

 mysql> select count(*) from table where schedule_time between '2013-08-16 12:48:00' and '2013-08-17 12:48:00'; +----------+ | count(*) | +----------+ | 19440 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from table where schedule_time between '2013-07-17 12:48:00' and '2013-08-17 12:48:00'; +----------+ | count(*) | +----------+ | 597132 | +----------+ 1 row in set (0.00 sec) 

Server Version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Answer 1

The MySQL optimizer tries to do the fastest thing - when it thinks that using the index will take so much time or longer than performing a table scan, it will abandon the available index. This is what you see in your examples: where the range is small (1 day), the index will be faster; where the range is large, you will attack a much larger table, which you could also scan the table directly (remember that using the index includes searching for the index and capturing indexed records from the table - it looks for two sets).

If you think you know better than the optimizer (this is not ideal), use the hints ( http://dev.mysql.com/doc/refman/5.5/en/index-hints.html ).