MySQL: filter date column using LIKE or MONTH?

I have a CRON task that needs to extract customers with birthdays in a given month, from a MySQL InnoDB table. The birthday field is indexed and of type DATE.

Filtering for April, I can query the customers table either by:

SELECT * 
FROM customers 
WHERE birthday LIKE "2015/04/%";

or:

SELECT * 
FROM customers 
WHERE MONTH(birthday) = 4;

Which one would you recommend, and why?

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type.

Using MONTH(birthday) allows MySQL to grab the month portion of the birthday column that it knows adheres to the DATE data format. When you use LIKE, it's doing character-by-character pattern matching which is significantly costlier and slower to do.

If LIKE was sufficient, then MONTH() wouldn't exist. Any built in function is always going to be the choice over LIKE for a DATE query.

StackExchange Database Administrators Q#96245, answer score: 11