Remember, a low cardinality index will possibly be ignored by the optimizer and an index on month will never have a cardinality of more than 12. For testing purposes, you might try added a column for month and populating it off your current data.
update the_table set the_field=MONTH(the_field) Then, add an index on that column and test your queries against the new index. I'd be surprised if you saw much increase in speed, especially as your data set grows. Greg On Wednesday 09 February 2005 09:17 am, Gabriel B. wrote: > Short Version: > is there any way to make an index for a date field that appears in the > WHERE as a MONTH() argument? > > > I have a table with some hundreds of thousands of rows already, and > now i have the need to show upcoming birthdays to some users. > > the query uses WHERE MONTH(birthday).. the `birthday` field is of date > (0000-00-00) type > > It is not too slow this way since i started the WHERE with lots of > checks that cuts down to a medium of 200 rows that actualy gets to > this check, but i feel uncorfotable to not use a index. > > So, is there any way to make an index for that kind of query, or i > must re-estruct the table to have a month field? > > Thanks! > Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
