0

I do not understand why MySQL is not using the index for the below query.

I have a table named users and its create table is

enter image description here

this table contains data.

Now I am running SQL select statements and it's checking its query execution plan.

for the statement

desc select *
     from   users
     where  users.created_at between '2019-05-04' and '2019-05-06';

output is enter image description here

but which I am using DateDiff function query is not using indexes.

desc select *
     from   users
     where  DATEDIFF(users.created_at,'2019-05-05 23:11:56.547') =  0;

and output for this is enter image description here

according to the MySQL documentation page "A function is nondeterministic if, given fixed values for its arguments, it can return different results for different invocations".

According to doc, DateDiff is a deterministic function and I am not understanding why MySQL is not taking indexes for the query.

9
  • A deterministic function is needed for a generated column which you can also index. But MySQL does not know how to turn a function into a rangenscan for the underlying base column (index)
    – eckes
    May 5 '19 at 19:16
  • So how can I Fix this? By adding a index something like this ? May 6 '19 at 1:57
  • Users(DateDiff(created_at)) ??? DateDiff requires two values. In my case one is table value and other is constant. And constant values can change on time. May 6 '19 at 1:58
  • So how should I make a derived coloum ? May 6 '19 at 2:02
  • You probably can’t use datediff if the constant changes. In this case it’s easier to have a column (derived or primary) which contains a flexible number representation (millis since epoch) instead, then you can use numerical compares which work with index.
    – eckes
    May 6 '19 at 5:00
0

Reason for indexes not being used is table column is an input part of the function. Current SQL Query

select *
 from   users
 where  DATEDIFF(users.created_at,'2019-05-05 23:11:56.547') =  0;

Indexes will be used if the query is in below way

select *
 from   users
 where  users.created_at =  DATE_ADD('2019-05-05 23:11:56.547', INTERVAL 0 DAY);

since above refers to a point of time. To check day we can have to use range timestamp start of today to start off tomorrow,

select *
 from   users
 where  users.created_at between DATE_ADD('2019-05-05 ', INTERVAL 0 DAY)
and DATE_ADD('2019-05-05 ', INTERVAL 1 DAY);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.