10

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!!

qinHaiXiang
  • 6,051
  • 12
  • 47
  • 61

3 Answers3

15

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).

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    I am new to mysql . So I am not sure what kind of index should I add on? I think I had made index on them: INDEX `date` (`date`, `user_id`) – qinHaiXiang Nov 20 '10 at 14:47
  • You want it the other way around as `user_id` is a "static" value, as `date` is passed to a function. – Danosaure Nov 20 '10 at 17:14
  • See https://dba.stackexchange.com/a/140693/39319 where user explains better how to cover all date and time periods if you are concern about midnight transactions – Alwin Kesler Jan 09 '18 at 18:18
  • Functions or cast on the date/datetime column affects performance, and nullifies the index on the column (if you added index, for performance boost) – Peter Jun 14 '19 at 18:05
2
SELECT *
FROM yourTable
WHERE datatime
BETWEEN '2010-10-01' AND '2010-11-01'

efficient and accepts indexing too.

heav3n
  • 77
  • 1
  • 7
0

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?

Edward Falk
  • 9,991
  • 11
  • 77
  • 112