MySQL BETWEEN two datetimes does not use INDEX
between, mysql
Solution
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)
Problem
``` 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