> 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]
