mysql日期怎么优化_如何在Mysql中优化日期时间搜索？

this is my basic date-time structure:

primary key(datetime) key

auot_id date_time user_id

1 2010-10-01 20:32:34 1

2 2010-10-02 20:32:34 1

3 2010-11-03 20:32:34 2

4 2010-10-04 20:32:34 1

5 2010-11-05 20:32:34 1

And I want to get the result of the day(date_time) at '2010-10' and the user_id = '1';

My SQL is:

SELECT * FROM datetime WHERE user_id = 1 AND DATE_FORMAT(date,'%Y-%m') = '2010-10'

but the EXPLAIN code display:

SIMPLE datetime ALL (NULL) (NULL) (NULL) (NULL) 5 Using where

so ,this line of code doesn't seem to be very effectively。How could I to construct the table to make my search more effective??

Thank you very much!!

解决方案

Using a function on a column in a WHERE clause prevents efficient usage of an index on that column. Try this instead:

SELECT *

FROM `datetime`

WHERE user_id = 1

AND `date` >= '2010-10-01' AND `date` < '2010-11-01'

Add an index on (user_id, date).