当与'<'一起使用时，MySQL在DATE不使用索引或“>"操作员? [英] MySQL not using index on DATE when used with '<' or '>' operators?

问题描述

我正在使用解释测试这些查询.列队类型为DATE

I'm using explain to test these queries. The col type is DATE

这使用索引:

explain SELECT events.* FROM events WHERE events.date = '2010-06-11' 

这不是

explain SELECT events.* FROM events WHERE events.date >= '2010-06-11' 

索引如下(phpmyadmin)

index as follows (phpmyadmin)

Action  Keyname Type    Unique  Packed  Field   Cardinality Collation   Null    Comment
Edit    Drop    PRIMARY BTREE   Yes No  event_id    18  A       
Edit    Drop    date    BTREE   No  No  date    0   A       

我注意到基数为0，尽管有些行具有相同的日期.

i notice cardinality is 0, though there are some rows with the same date..

推荐答案

如果MySQL不使用索引，则它已经看到了您的查询，并估计表扫描可能比使用索引要快(可能需要IO/磁盘操作).您可以使用FORCE INDEX并使用索引检查此查询是否实际上会更快.

If MySQL doesn't use the index, it has seen your query, and estimated that a table scan would probably be faster then using the index (in terms of IO / disk operations required probably). You can use a FORCE INDEX and check whether this query will actually be faster using the index or not.

SELECT events.* FROM events
FORCE INDEX (date)
WHERE events.date >= '2010-06-11';

这篇关于当与'<'一起使用时，MySQL在DATE不使用索引或“>"操作员?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！