key on the month portion of a date field
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 (-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]
Re: key on the month portion of a date field
use below: alter table table_name add index (birthday); Love Kumar .. [EMAIL PROTECTED] 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 (-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] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation.
Re: key on the month portion of a date field
On Wed, 09 Feb 2005 17:24:10 +, love [EMAIL PROTECTED] wrote: alter table table_name add index (birthday); But would that index improve this kind of query? the docs talk about only direct matchs, like birthday now() or birthday between x and y. They're all full date values. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
At 15:37 -0200 2/9/05, Gabriel B. wrote: On Wed, 09 Feb 2005 17:24:10 +, love [EMAIL PROTECTED] wrote: alter table table_name add index (birthday); But would that index improve this kind of query? the docs talk about only direct matchs, like birthday now() or birthday between x and y. They're all full date values. It won't help except for queries that test the value of birthday directly. As soon as you use the column in a calculation such as a function call, the index can't be used. To use month in indexed fashion, you could store dates as separate year, month, and date columns. But that might be more of a hassle than you want to deal with. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
On Wed, 9 Feb 2005 15:17:36 -0200, Gabriel B. [EMAIL PROTECTED] wrote: 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. Isn't there a limit of 1 index per table in a query? If you're already using where statements to eliminate rows, I'm assuming that you're already using an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
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 (-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]
