4

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?

  • i would go for the second query as it will give you the exact records for the month. SELECT * FROM customers WHERE MONTH(birthday) = 4; – solibaz Aug 19 '15 at 12:29
9

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.

4

(More of a bunch of comments, plus a redundant answer.)

  • Your two examples are not identical -- one is limited to 2015; the other is not.
  • WHERE birthday BETWEEN '2015-04-01' AND '2015-04-01' + INTERVAL 1 MONTH would be able to use INDEX(birthday), but that only covers those who will be born next month.
  • Even if you had a mnth TINYINT UNSIGNED COMMENT 'derived from MONTH(birthday), INDEX(mnth) is might not be used. This is because a 'large' percentage of the the table is desired. The optimizer might decide that a table scan is as fast.
  • So, I agree with the others: WHERE MONTH(birthday) = 4 is probably the 'best' answer.
4

I would go for:

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

...since it's the common way to select by month. Using MONTH() will call a built in function in MySQL, but if you use LIKE it will take it as character then do the comparison, so instead of making one operation MySQL will execute two.

1

I have tested for 100.000 row

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

SELECT * FROM customers WHERE birthday LIKE "2015/04/%"; ==> 21 second

  • Both of those hide birthday in a function. The second one may not look hidden, but it is converting it to a string. – Rick James Aug 19 '15 at 17:57
  • If more than one year is involved, then the first could give more results. – Rick James Aug 19 '15 at 17:58

protected by Paul White Aug 19 '15 at 12:36

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.