Mysql: How to properly use and index the date time field in mysql where clause?

Question

We have listings table where we keep all our products. The products have expiry determined by column "ExpiryDate" in the listings table. We want to get all the listings that have expired a day before:

Previous query:

SELECT * from listings
WHERE DATE(ExpiryDate + interval 1 day) = DATE(now());

Modified query:

SELECT * from listings
WHERE ExpiryDate between DATE_FORMAT(now()- interval 1 day, '%Y-%m-%d 00:00:00') and DATE_FORMAT(now() -interval 1 day, '%Y-%m-%d 23:59:59');

I read it somewhere that in "previous query", we can't use indexing on datetime while we can leverage indexing optimization in the "modified query". Is that correct? And is the "modified query" best tuned for performance if we put indexing on column "ExpiryDate"?

First query Add 1 day to every record, then check if it's the same as now() - you can't use indexes because you're modifying the stored value. Had you written it in a different way - Check if the date is same as (now() minus 1 day) then MySQL can use indexes since it will calculate which number now() - 1 day corresponds to and can use it as comparison base, which is exactly what the second query does. It does not modify the ExpiryDate before comparing it to a value. It modifies the VALUE it compares ExpiryDate against. — Mjh
– Mjh, Commented Aug 1, 2017 at 11:38
Is the query you're using tuned? You have EXPLAIN, EXPLAIN EXTENDED and profiling available which you can use for measuring. If your ExpiryDate is a datetime and if it's indexed, then you should be fine. If it's still slow, it's time to tune the hardware, what resources are available to MySQL (look up innodb_buffer_pool_size) etc. — Mjh
– Mjh, Commented Aug 1, 2017 at 11:41

Rick James · Accepted Answer · 2017-08-14 17:12:12Z

2

The basic rule is to avoid hiding an indexed column in a function call. In your "previous" query it is both DATE() and also + INTERVAL, which is effectively a function call.

Also, several simplifications can be made to say "did it expire yesterday":

WHERE ExpiryDate >= CURDATE() - INTERVAL 1 DAY
  AND ExpiryDate  < CURDATE()

And you need INDEX(ExpiryDate).

innodb_buffer_pool_size is generally important, and should be about 70% of available RAM. However, the buffer_pool is probably big enough. Profiling rarely says anything actionable.

answered Aug 14, 2017 at 17:12

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql: How to properly use and index the date time field in mysql where clause?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related