分享到:
发表于 2009-05-18 16:56:26 楼主 | |
report 表索引字段 calldate(datetime)
查询1: explain SELECT * FROM report WHERE calldate >= '2009-04-09 00:00:00'; +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | report | ALL | calldate | NULL | NULL | NULL | 128305 | Using where | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) 查询2: explain SELECT * FROM report WHERE calldate >= '2009-04-10 00:00:00'; +----+-------------+----------+-------+---------------+----------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+----------+---------+------+-------+-------------+ | 1 | SIMPLE | report | range | calldate | calldate | 8 | NULL | 23029 | Using where | +----+-------------+----------+-------+---------------+----------+---------+------+-------+-------------+ 1 row in set (0.00 sec) 为什么两个时间字段的范围不一样就导致了索引的使用 |
|
楼主热贴
个性签名:
|
发表于 2009-05-18 16:56:46 1楼 | |
对 楼主 趣昧鱼 说: ========================= 呃,这正是mysql聪明的地方,不排除小聪明的可能 |
|
发表于 2009-05-18 16:56:57 2楼 | |
我把2008-04-09以前的数据删除,发现记录在2009-04-17是个界限,这个真的头一次碰到很郁闷的事。。 | |
发表于 2009-05-18 16:57:15 3楼 | |
此时mysql优化器认为整表的读取更优,所以没有使用索引。 你可以使用 SELECT * FROM report use index(calldate) WHERE calldate >= \'2009-04-10 00:00:00\'; 来强制使用索引 |
|