Alden W. Alden W. - 1 month ago 10
MySQL Question

INDEX on DATE field and MONTH() YEAR() functions

Is it still correct than an index on a date/datetime column is not optimized for YEAR(col), MONTH(col) functions? Bill Karwin gave a pretty definitive answer here, but since this was from ten years ago I wanted to check. I would have thought that since the date column is described as


A three-byte integer packed as YYYY×16×32 + MM×32 + DD


that it could be indexed in such a way that it could be optimized in the same way that multi column indexes are when a tight index scan is performed.

Is it true this optimization still does not exist? And why wouldn't it be possible?

Answer

The problem is not the representation of the date. The problem is the optimization of the query. When you use YEAR() and MONTH() on a date column, the column is a function of an argument.

That means that the compiler would have a lot of information about the function in order to optimize it using an index scan or index lookup. Such as:

  • The function would have to return the same values given the same arguments.
  • The function would take only one argument.
  • The function would need to be monotonic.
  • The function would need to have an inverse (for starting an index scan).

Of course, these things are possible. The challenge is actually building them into a function definition and into the optimizer. No one who builds SQL optimizers would want to add special cases for a specific function (well, almost no one; SQL Server does allow some use of cast() to take advantage of an index).

So, the answer to your question is that Bill's answer is still valid.