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.
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.
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
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
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.
(emphasis mine)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With