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

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 <tpblists@terranovum.com> 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/mysql?unsub=drbuettner@gmail.com
>
>

> 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 <tpblists@terranovum.com> 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/mysql?unsub=drbuettner@gmail.com
>>
>>
>

--
Anders Lundgren
Viba IT Handelsbolag
Webb: http://www.vibait.se
E-post: kontakt@vibait.se
Mobil: 070-55 99 589

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.

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: kontakt@vibait.se
Cell: +46 (0)70-55 99 589

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" <anders@vibait.se>
To: "Dan Buettner" <drbuettner@gmail.com>
Cc: "Thomas Bolioli" <tpblists@terranovum.com>; <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 <tpblists@terranovum.com> 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/mysql?unsub=drbuettner@gmail.com
>>>
>>>
>>
>
> --
> Anders Lundgren
> Viba IT Handelsbolag
> Webb: http://www.vibait.se
> E-post: kontakt@vibait.se
> Mobil: 070-55 99 589
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>

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" <anders@vibait.se>
> To: "Dan Buettner" <drbuettner@gmail.com>
> Cc: "Thomas Bolioli" <tpblists@terranovum.com>; <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 <tpblists@terranovum.com> 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
>>>>