CODEWITHSUNDEEP
mysqlbetween
GGio
GGio

Reputation: 7653

MySQL BETWEEN two datetimes does not use INDEX

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

Upvotes: 8

Views: 11377

Answers (2)

qingjiuzhubei
qingjiuzhubei

Reputation: 1

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.

the other way ,you can add “limit” to your sql also worked for me

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

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)

Upvotes: 4

Related Questions