Hi everybody,

I have spent many hours rewriting queries that use date functions in there where clause. They can quite easily be rewritten but I've also worked with 3rd party closed source tools that couldn't easily change their query structure.

Example: SELECT * FROM payments WHERE YEAR(creationDate) = 2015 AND MONTH(creationDate) = 9 AND method='creditcard'
This can be rewritten to: SELECT * FROM payments WHERE creationDate BETWEEN "2015/09/01 0:00:00" AND "2015/10/01 0:00:00" AND method='creditcard'

Currently, only the 'creditcard' part of a creditcard_creationDate compound index. After rewriting, the full index can be used. If creditcard payments represent 90% of the payments it would basically be a full table scan.

Thank you,
Michael