MySQL BETWEEN two datetimes does not use INDEX

Question

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

What if you substitute BETWEEN for ('2014-02-24' <= Created AND Created < '2014-02-25') ? — vadchen
– vadchen, Commented Feb 24, 2014 at 16:00
Already did that in different variations still does not use index — GGio
– GGio, Commented Feb 24, 2014 at 16:05
Please post structures of tables, or at least add prefixes to column names in the query, it's hard to guess which column belong to which table. Does NoteVisible belong to Notes ? — krokodilko
– krokodilko, Commented Feb 24, 2014 at 16:27
yes NoteVisible belongs to notes why would it belong to any presented tables? — GGio
– GGio, Commented Feb 24, 2014 at 17:04

Community · Accepted Answer · 2020-06-20 09:12:55Z

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)

but there are not 23 rows in that range if I remove explain it only returns 1 row.
This is only an estimation - MySql thinks that there is 23 rows. It doesn't check an exact number of rows during the parse phase, it only estimates. But regardles of that, it thinks that it is better to use that index and join 23 rows than to use the primary key and join X rows.

qingjiuzhubei · Accepted Answer · 2017-05-25 10:15:15Z

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

Collectives™ on Stack Overflow

MySQL BETWEEN two datetimes does not use INDEX

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related