Really what you are trying to do is search on month + day, not a date.
For special "dates" (birthday, anniversary, etc), I always store the
year separately. Especially since some people don't really want you to
know how old they are.
Without breaking the "date" up into it's separate parts, you can't use
an index, so you will always do a full table scan. Your searches will
get slower as you add more records. I don't know how many records you
are searching on in your example, but if you have a lot, the difference
is pretty minimal and may be due to slightly different loads on the
computer. Although the DATE_FORMAT one has the extra overhead of
formating every single record to do the comparison.
On Nov 18, 2004, at 4:28 AM, Jigal van Hemert wrote:
> 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 ?
>
> Regards, Jigal.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
