chastang wrote:
select * from my_table where hour(dt)= 0
or
select * from my_table where month(dt) = 6
What index should I create to optimize selects on these sorts of queries?
An index isn't going to help you there unless you create separate columns for hour and month. The columns will be redundant data, but they should speed up queries. The index on the DATETIME column is useless because those queries aren't looking for a contiguous range of times; they're looking for times fitting in lots of little ranges scattered throughout the possible range of times.
Now, if you were only looking for times with hour 0 on one particular day, the index should help, if you have a query like this:
SELECT * FROM my_table WHERE dt BETWEEN '2004-06-24 00:00:00' AND '2004-06-24 00:59:59';
Similarly, looking for records for a particular month in a particular year should work:
SELECT * FROM my_table WHERE dt BETWEEN '2004-06-01 00:00:00' AND '2004-06-31 23:59:59';
But that's apparently not what you want. Your needs are a little unusual, so they will require a table structure that's a little unusual.
-- Keith Ivey <[EMAIL PROTECTED]> Washington, DC
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
