mysql时间戳排序索引_时间戳列上的MySQL索引不用于大日期范围

我有桌子

+-------------------+----------------+------+-----+---------------------+-----------------------------+

| 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 | |

+-------------------+----------------+------+-----+---------------------+-----------------------------+

我的schedule_time索引为

create index table_index on table(schedule_time asc);

该表目前有2121552条记录.

我无法理解的是我解释的时候

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)

使用上面的索引,但下面没有.

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)

我真的很感激,如果有人可以在这里指出什么是错误的,因为数据每12分钟更新一次,并且随着查询时间超过30天或者可能是60天将变得非常缓慢.

我计划使用它的最终查询如下

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;

更新=====>

根据之间的评论也失败了.

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)

更新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)

服务器版本：5.5.24-0ubuntu0.12.04.1(Ubuntu)