Why does MySQL drops my index when using DATE(`table`.`column`)

Question

I have a MySQL innodb table with a few columns. one of them is named "dateCreated" which is a DATETIME column and it is indexed.

My query:

        SELECT 
            *
        FROM 
            `table1`
        WHERE 
            DATE(`dateCreated`) BETWEEN '2014-8-7' AND '2013-8-7'

MySQL for some reason refuses to use the index on the dateCreated column (even with USE INDEX or FORCE INDEX.

However, if I change the query to this:

        SELECT 
            *
        FROM 
            `table1`
        WHERE 
           `dateCreated` BETWEEN '2014-8-7' AND '2013-8-7'

note the DATE(...) removal

MySQL uses the index just fine. I could manage without using the DATE() function, but this is just weird to me.

I understand that maybe MySQL indexes the full date and time and when searching only a part of it, it gets confused or something. But there must be a way to use a partial date (lets say MONTH(...) or DATE(...)) and still benefit from the indexed column and avoid the full table scan.

Any thoughts..?

Thanks.

MySQL can't use indexes on expressions like this. Because of that take care that you use functions only on the right side of a comparison operator. — VMai, Aug 07 '14 at 08:33
Have a look at http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql — VMai, Aug 07 '14 at 08:37

Paul Maxwell · Answer 1 · 2014-08-07T08:58:58.190

As you have observed once you apply a function to that field you destroy access to the index. So,

It will help if you don't use between. The rationale for applying the function to the data is so you can get the data to match the parameters. There are just 2 parameter dates and several hundred? thousand? million? rows of data. Why not reverse this, change the parameters to suit the data? (making it a "sargable" predicate)

    SELECT 
        *
    FROM 
        `table1`
    WHERE 
         ( `dateCreated` >= '2013-08-07' AND `dateCreated` < '2014-08-07' )

    ;

Note 2013-08-07 is used first, and this needs to be true if using between also. You will not get any results using between if the first date is younger than the second date.
Also note that exactly 12 months of data is contained >= '2013-08-07' AND < '2014-08-07', I presume this is what you are seeking.
Using the combination of date(dateCreated) and between would include 1 too many days as all events during '2014-08-07' would be included. If you deliberately wanted one year and 1 day then add 1 day to the higher date i.e. so it would be < '2014-08-08'

Why does MySQL drops my index when using DATE(`table`.`column`)

1 Answers1