Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL BETWEEN two datetimes does not use INDEX

Tags:

mysql

between

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

enter image description here

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

like image 336
GGio Avatar asked Feb 24 '14 15:02

GGio


1 Answers

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)

like image 130
krokodilko Avatar answered Oct 13 '22 06:10

krokodilko