Where datetime=date without full table scan?

Question

Possible duplicate of: How to select date from datetime column?

But the problem with the accepted answer is it will preform a full table scan.

I want to do something like this:

UPDATE records SET earnings=(SELECT SUM(rate) 
FROM leads 
WHERE records.user_id=leads.user_id 
   AND DATE(leads.datetime)=records.date)

Notice the last portion: DATE(leads.datetime)=records.date. This does exactly what it needs to do, but it has to scan every row. Some users have thousands of leads so it can take a while.

The leads table has an INDEX on user_id,datetime.

I know you can use interval functions and do something like WHERE datetime BETWEEN date AND interval + days or something like that.

What is the most efficient and accurate way to do this?

Charles Bretana · Accepted Answer · 2013-07-29T12:51:02.380

I'm not familiar with date functions in MySQL, but try changing it to

UPDATE records SET earnings=
  (SELECT SUM(rate) 
   FROM leads 
   WHERE records.user_id=leads.user_id 
     AND  leads.datetime >= records.date
     And  leads.datetime < records.date [+ one day])  -- however you do that in MySQL

You are getting a complete table scan because the expression DATE(leads.datetime) is not Sargable. This is because it is a function which needs to operate on the value stored in a column of the table, and which is also stored in any index on that column. The function's value, obviously, cannot be pre-computed and stored in any index, only the actual column value, so no index search can identify which rows will, after having the function executed on them, meet the criteria expressed in the Where clause predicate. Changing the expression so that the column value is, by itself on one side or the other of the where clause operator, (equal sign or whatever), allows the column values in the index to be searched based on a single expression.

score 2 · Answer 2 · answered Jul 29 '13 at 03:24

You can try this:

UPDATE records
    SET earnings = (SELECT SUM(rate)
                    FROM leads
                    WHERE records.user_id=leads.user_id AND
                          leads.datetime >= records.date and
                          leads.datetime < date_add(records.date, interval 1 day)
                   );

You need an index on leads(user_id, datetime) for this to work.

Where datetime=date without full table scan?

2 Answers2