Hello.
You're right. I haven't understood the problem properly. Terence <[EMAIL PROTECTED]> wrote: > Wouldnt that only work for the current year? For example I was born on > 1970-08-25, "select id from members where birthday = now();" wouldnt > return my birthday if it was today. Or am I missing something new in > 5.0.11? > > > > Gleb Paharenko wrote: >> Hello. >> >> Why don't you want to use just: >> select id from members where birthday = now(); >> >> See: >> >> mysql> show create table members\G; >> *************************** 1. row *************************** >> Table: members >> Create Table: CREATE TABLE `members` ( >> `id` int(11) NOT NULL auto_increment, >> `birthdate` date default NULL, >> PRIMARY KEY (`id`) >> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 >> 1 row in set (0.00 sec) >> >> >> mysql> select * from members; >> +----+------------+ >> | id | birthdate | >> +----+------------+ >> | 1 | 2004-02-29 | >> | 2 | 2005-02-28 | >> | 3 | 2005-08-24 | >> +----+------------+ >> >> mysql> select id from members where birthdate = now(); >> +----+ >> | id | >> +----+ >> | 3 | >> +----+ >> >> mysql> select now(); >> +---------------------+ >> | now() | >> +---------------------+ >> | 2005-08-24 20:27:20 | >> +---------------------+ >> 1 row in set (0.00 sec) >> >> mysql> select version(); >> +-----------------------+ >> | version() | >> +-----------------------+ >> | 5.0.11-beta-debug-log | >> +-----------------------+ >> 1 row in set (0.00 sec) >> >> If you add an index on birthdate, you can avoid table scans. >> >> create index `birthdate` on members(birthdate); >> >> mysql> explain select id from members where birthdate = now()\G; >> *************************** 1. row *************************** >> id: 1 >> select_type: SIMPLE >> table: members >> type: ref >> possible_keys: birthdate >> key: birthdate >> key_len: 4 >> ref: const >> rows: 1 >> Extra: Using where >> 1 row in set (0.00 sec) >> >> >> >> Pooly <[EMAIL PROTECTED]> wrote: >> >>>Hi, >>> >>>I would like to display a list of members who have their birthday a >>>given day (today for instance). >>>My idea is to store their birth date in a column, and then query the >>>table against the column. But the query would be like : >>>select id from members where MONTH(birthday) =3D MONTH(NOW()) AND >>>DAY(birthday)=3DDAY(NOW()) >>>but it would perform a entire table scan with that. >>>What would be your best strategy for that sort of query ? >>>And how would you deal with 29th of february ? >>> >>>--=20 >>>Pooly >>>Webzine Rock : http://www.w-fenec.org/ >>> >> >> >> > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
