Hello.
My question concerns a large data table that has a DATETIME column called dt. Specifically, I am often having to do selects based on date and time functions that look like this:
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? ["create index my_index on my_table(dt)" does not help.]
Thanks. -JC
JC,
Any time you apply a function to a column, like hour(dt), MySQL cannot and will not use the index. If you think about it, how is MySQL going to use a date index to find the rows in the 5th hour? If you have less than 1 year of data, then you could rewrite the date query it as:
select * from my_table where dt >= '2004-06-01' and dt <= '2004-06-30'
Otherwise you will need to create a dt_Month column and dt_Hr column in the table and compute these values when the data gets added. If you already have data in the table, you will need to write an sql statement to update these new columns.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
