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/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]