Re: efficient query for: "it's your birthday today"

[Rhino]

----- Original Message ----- 
From: "Jigal van Hemert" <jigal@stripped>
To: <mysql@stripped>
Sent: Thursday, November 18, 2004 4:28 AM
Subject: efficient query for: "it's your birthday today"


> I have date of birth stored in a DATETIME column and need to find for
which
> persons a certain date is their birthday.
>
> I've tried so far:
>
>     DATE_FORMAT (col, '%m%d') = '1107'        => 0.2001 sec
>
>     col LIKE '%-11-07%'        => 0.1643 sec
>
>     col RLIKE '-11-07'        => 0.1702 sec
>
> Are there faster alternatives for MySQL 4.0.21 ?
>
I don't know if it is more efficient but I would write this query as
follows, simply because it is easier to understand what it is doing when you
look at it:

select [whatever columns you want]
from mytable
where month(birthdate) = 11
and dayofmonth(birthdate) = 7;

Rhino