mysql - DATE 的索引(列)

Question

如果我使用以下代码比较 date 和 datetime

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

我还能在 startTime 上使用索引吗？

创建索引后:

create index mi_date on calendar(startTime);

解释结果:

+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | calendar     | index | NULL          | mi_date | 6       | NULL |   25 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+

和查询

SELECT * FROM `calendar` WHERE startTime like '2010-04-29 %'

解释命令:

+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | calendar     | index | mi_date       | mi_date | 6       | NULL |   25 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+

第一个解释之间的区别在于 possible_keys 列不为空。

还有这个查询:

SELECT * FROM `calendar` WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'

解释:

+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | calendar     | range | mi_date       | mi_date | 6       | NULL |   16 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+

列类型是范围，扫描的行少于 16，这是我查询的结果。

p.s:我的表中有 25 行。

Answer 1

用解释

mysql> EXPLAIN SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

您可以使用 mysql 检查索引。但通常 MySQl 不会使用 DATE() 使用索引。

尝试

SELECT * FROM `calendar` WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'