MySQL DATEDIFF function VS compare INTERVAL DAY

What is the difference between DATEDIFF function and subtract INTERVAL DAY directly?

1. SELECT * FROM table WHERE DATEDIFF(CURDATE(), publish_date) <= 3

2. SELECT * FROM table WHERE publish_date >= CURDATE() - INTERVAL 3 DAY

Result data are the same, but it seems 2. way is a bit faster?

Answer

The first expression, that uses DATEDIFF() requires applying the date function on each and every row before the filtering can happen.

By contrast, the second expression does not imply such pre-processing: CURDATE() - INTERVAL 3 DAY is computed just once, and then compared directly against the value of publish_date. This predicate can take advantage of an index on the date column. This is the right way to do it.

In technical terms, we say that the second predicate is sargable, while the first one isn’t: this stands for Search ARGument ABLE

As a rule of thumb: do not apply functions the column that you filter on if you have a way around.