当前位置: 动力学知识库 > 问答 > 编程问答 >

datetime - How to optimize date time search in 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).

网友答案:
SELECT *
FROM yourTable
WHERE datatime
BETWEEN '2010-10-01' AND '2010-11-01'

efficient and accepts indexing too.

网友答案:

How about:

WHERE CAST(datatime AS DATE) = '2010-10-01'

Or perhaps

WHERE CAST(datatime AS DATE) = CAST('2010-10-01' AS DATE)

Would that be too inefficient?

分享给朋友:
您可能感兴趣的文章:
随机阅读: