MySQL-mysql 组合索引

Question

1张千万表 id,type,status,create_time 等字段
组合索引，(type,status,create_time)

现在需要查询时间段内的记录，
eg：select * from table where type=0 and status=1 and create_time > '2013-10-11 10:10:10' and create_time < '2014-10-11 10:10:10'

使用一个 create_time 时，索引有效，两个create_time 索引失效

Answer 1

组合索引满足最左原则，你的那个就符合前三个，你改成between .. and 更好，理解也快，

Answer 2

select * from table where type=0 and status=1 and (create_time > '2013-10-11 10:10:10' and create_time < '2014-10-11 10:10:10');

这种在某些版本上会转换成 between ... and
但是写成

select * from table where type=0 and status=1 and create_time between '2013-10-11 10:10:10' and '2014-10-11 10:10:10';

不是更可读，更好理解吗？