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]