Google+ Followers

zondag 23 maart 2014

SQL Performance thingies , part 1. Don't modify a datetime to compare against NOW().

When searching for records of a particular age, people tend to do this:

SELECT * FROM table WHERE (NOW() - datefield) >= INTERVAL 5 DAY;

or

SELECT * FROM table WHERE datefield + INTERVAL 5 DAY >= NOW()

These are bad because they require the database to calculate the difference for all records, which is a no-index operation.

The best way to do this is to calculate the actual date  value that marks '5 days ago'. ie: NOW() - INTERVAL 5 DAY:


SELECT * FROM table WHERE datefield >= NOW()  - INTERVAL 5 DAY;

This way the database will see that NOW()-INTERVAL 5 DAY is a constant within the transaction, and calculate it only once. Comparing a date field against a constant can use an index so this is the fastest way of doing this.