Home Ask Login Register

Developers Planet

Your answer is one click away!

John Michael Tolentino 3 weeks ago

Index on date field mysql

I have a table screenshot with 3 fields:

ID int (PK)
UserID int 
DateTaken date (INDEX)

Query:

SELECT COUNT(ID) total
        FROM screenshot WHERE DateTaken BETWEEN '2000-05-01' AND '2000-06-10'

Result : 2828844

Explain output:

ID|select_type|   table  |type |possible_keys|   key   |key_len| rows  |Extra
1 |  SIMPLE   |screenshot|range|  DateTaken  |DateTaken|  3    |5730138|Using where; Using index

Here is my problem: I have added index to DateTaken column and yet the scanning rows (Explain output) is bigger than the result. It seems like it does a whole scan table. And the Query runtime for the query takes 15 seconds. How can I improve the speed in the query above?

Answers


Gordon Linoff 3 weeks ago

Try running this query:

SELECT COUNT(*) as total
FROM screenshot
WHERE DateTaken BETWEEN '2000-05-01' AND '2000-06-10';

The reference to ID in the SELECT could be affecting the use of the index.


scaisEdge 3 weeks ago

You could try adding a composite index

  create index test on screenshot (DateTaken, id)

Post Status

Asked in 3 weeks ago
Viewed 3,998 times
Voted 6
Answered 2 times

Search




Leave an answer


Quote of the day: live life