Re: Date v. DateTime index performance
OK, thank you. How is the speed of this index compared with an indexed date column if I do: year_number='x' and month_number='y' and day_number='z'; They should have about the same cardinality, right? Thanks, Anders Chris wrote: Anders Lundgren wrote: One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- Anders Lundgren Viba IT Handelsbolag Web: http://www.vibait.se E-mail: [EMAIL PROTECTED] Cell: +46 (0)70-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on = first day of the month and the first day of the next month. That will use an index. - Original Message - From: Anders Lundgren [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Thomas Bolioli [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Yes, of course. Thank you! - Anders Brent Baisley wrote: Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on = first day of the month and the first day of the next month. That will use an index. - Original Message - From: Anders Lundgren [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Thomas Bolioli [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Anders Lundgren wrote: One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date v. DateTime index performance
If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
