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
As you see ref is NULL and goes through 23 rows instead of just going through 1 row. Now for this example this is fast but when I do range of 1-2 month the rows becomes >10000 and it slows down the page alot and locks up tables.
如您所见，ref为NULL并经过23行而不是仅通过1行。现在对于这个例子来说这很快但是当我做1-2个月的范围时，行变为> 10000并且它减慢了页面的速度并锁定了表格。
NOTE If I remove the 00:00:00 and 23:59:59 then it uses index it only goes through 1 row but I need to select all data for entire day starting at 00:00 and ending at 23:59.
注意如果我删除00:00:00和23:59:59然后它使用索引它只通过1行但我需要选择从00:00开始到23:59结束的整天的所有数据。
Please help me restructure this query to fix this issue or suggest any possible solutions. thank you.
请帮我重构此查询以解决此问题或建议任何可能的解决方案。谢谢。
EDIT
编辑
Replacing BETWEEN by < or > or <= or >= does not fix the issue
用 <或> 或<=或>替换BETWEEN不能解决问题
2 个解决方案
#1
4
This query uses the index.
The select type is range, used key is Created
For range types, the ref column is always null,
refer to documentation: http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range
此查询使用索引。 select类型是range，used key is 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.
仅检索给定范围内的行，使用索引选择行。输出行中的键列指示使用哪个索引。 key_len包含使用的最长密钥部分。对于此类型，ref列为NULL。
(emphasis mine)
（强调我的）
#2
0
when you are comparing a datetime field with strings in a greater_than/less_than comparison. If you use a cast or function (like UNIX_TIMESTAMP()) and transform also the other dates to unixtimestamp, that would do the trick but will destroy the use of the index. Maybe a better solution would be to store the date as unix timestamp in the table and put an index on that.
当您在date_than / less_than比较中将datetime字段与字符串进行比较时。如果您使用强制转换或函数（如UNIX_TIMESTAMP（））并将其他日期转换为unixtimestamp，那将会解决问题，但会破坏索引的使用。也许更好的解决方案是将日期作为unix时间戳存储在表中并在其上放置索引。
the other way ,you can add “limit” to your sql also worked for me
另一方面，你可以为你的sql添加“限制”也为我工作
#1
4
This query uses the index.
The select type is range, used key is Created
For range types, the ref column is always null,
refer to documentation: http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range
此查询使用索引。 select类型是range，used key is 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.
仅检索给定范围内的行，使用索引选择行。输出行中的键列指示使用哪个索引。 key_len包含使用的最长密钥部分。对于此类型，ref列为NULL。
(emphasis mine)
（强调我的）
#2
0
when you are comparing a datetime field with strings in a greater_than/less_than comparison. If you use a cast or function (like UNIX_TIMESTAMP()) and transform also the other dates to unixtimestamp, that would do the trick but will destroy the use of the index. Maybe a better solution would be to store the date as unix timestamp in the table and put an index on that.
当您在date_than / less_than比较中将datetime字段与字符串进行比较时。如果您使用强制转换或函数（如UNIX_TIMESTAMP（））并将其他日期转换为unixtimestamp，那将会解决问题，但会破坏索引的使用。也许更好的解决方案是将日期作为unix时间戳存储在表中并在其上放置索引。
the other way ,you can add “limit” to your sql also worked for me
另一方面，你可以为你的sql添加“限制”也为我工作