How can I rewrite the following removing the date_format function?

Question

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index:
AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month )
How can I re-write this so that I can get rid of the DATE_FORMAT that forbids index usage?

Answer 1

The comment from @ChristianAmmer is the key to answering your question. Why ???

First look the the WHERE clause

DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month )

You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds wear-and-tear on the query. I have discussed this before (See my answer to What happens when we take a timediff between timestamp and datetime).

When comparing to LAST_DAY(now() - interval 1 month ), there is no need to do the DATE_FORMAT function against t1.when. Look at what actually happens when you do:

• You evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') and the result is a string
• The string has to be converted back to a DATE
• That converted DATE is then compared to LAST_DAY(now() - interval 1 month )

These steps must be done for every row.

To remove those steps, just switch the WHERE clause to have

AND t1.when <= LAST_DAY(now() - interval 1 month)

as @ChristianAmmer specified.

That way it compares DATE to DATE without any string manipulate or function call.

To answer your last comment

@ChristianAmmer:I was not sure if it is the same

The comparison of the result is the same, but the way the comparison is executed is not.

Doing it with AND t1.when <= LAST_DAY(now() - interval 1 month) will always be faster. Leaving the t1.when on the left side of the comparison (left side of the equal sign) obligates the MySQL Query Optimizer to try using the index. Using a function call on the left side of the comparison eliminates index usage.

CONCLUSION : Go ahead an d use AND t1.when <= LAST_DAY(now() - interval 1 month)

UPDATE 2014-04-15 10:46 EDT

To further prove function calls on the left side confuses the MySQL Query Optimizer, I have other posts going back to 2011 that discuss how searching a FULLTEXT index cause the MySQL Query Optimizer to stop using other indexes (since you have to use the MATCH function on the left side).

• May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
• Jan 26, 2012 : Mysql fulltext search my.cnf optimization
• Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
• May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)