Stop Using whereDate() Method on Indexed DateTime Field in Laravel

Suppose we have users table with index in created_at field, and we want to retrieve all users that registered in a specific date. We could use query builder with whereDate() method like this:

And the query would be something like this:

The query cost 1.51 s because sql DATE() function converts selected field into datestring which will precludes the use of an index. So, it will iterate over all records.

This is the explain query:

Solution

Instead of using whereDate(), we could use where() method like this:

The query would be something like this:

The query cost reduced to less than 10 ms.

We can look at this explain query that it is using index.

Conclusion

Avoid using whereDate() method on indexed date field because it will convert the field into datestring which will precludes the use of an index.

This also applies to whereDay(), whereMonth(), whereYear() and whereTime() methods.

Note

There is a closed issue about this in Laravel repository. It does make sense that whereDate() method is translated into sql DATE(), but the documentation should at least note about this behaviour and its side effect, which will prevent the use of indexes.

Builder::whereDate() prevents the use of indexes · Issue #41522 · laravel/framework

P.S.: If you want to study Laravel together and find more secrets, Join Us!