当与'<'一起使用时,MySQL在DATE不使用索引或“>"操作员? [英] MySQL not using index on DATE when used with '<' or '>' operators?
本文介绍了当与'<'一起使用时,MySQL在DATE不使用索引或“>"操作员?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用解释测试这些查询.列队类型为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屋!
查看全文