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 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
选择类型是范围,使用的键是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.
(强调我的)