> 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