MySQL BETWEEN两个日期时间不使用INDEX

概述explain SELECT COUNT(*) AS Count, CreatedBy FROM `Notes` INNER JOIN Users ON UserID = CreatedBy INNER JOIN UserRoles ON URoleID = RoleID AND RoleID = 1 WHERE NoteVisible = 1 AND No

explainSELECT COUNT(*) AS Count,CreatedByFROM `Notes`INNER JOIN Users ON UserID = CreatedByINNER JOIN UserRoles ON URoleID = RoleID AND RoleID = 1WHERE NoteVisible = 1 AND NoteText NOT liKE '%sOME KEYWORD%'       AND Created BETWEEN '2014-02-24 00:00:00' AND '2014-02-24 23:59:59' GROUP BY CreatedBy

如您所见,ref为NulL并且经过23行而不是仅通过1行.现在对于这个例子来说这很快但是当我做1-2个月的范围时,行变为> 10000并且它减慢了页面的速度并且锁定了表格.

注意如果我删除00:00:00和23:59:59然后它使用索引它只通过1行但我需要选择从00:00开始到23:59结束的整天的所有数据.

请帮我重构此查询以解决此问题或建议任何可能的解决方案.谢谢.

编辑

将BETWEEN替换为<或者>或< =或> =不能解决问题最佳答案此查询使用索引.
选择类型是范围,使用的键是Created

对于范围类型,ref列始终为null,
参考文件：http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range

range

Only rows that are in a given range are retrIEved,using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NulL for this type.

(强调我的) 总结

以上是内存溢出为你收集整理的MySQL BETWEEN两个日期时间不使用INDEX全部内容，希望文章能够帮你解决MySQL BETWEEN两个日期时间不使用INDEX所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。