Index on date field mysql

Question

I have a table screenshot with 3 fields:

CREATE TABLE `screenshot` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `UserID` int(11) NOT NULL,
  `DateTaken` date NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `DateTaken` (`DateTaken`),
  KEY `UserID` (`UserID`) USING BTREE,
  CONSTRAINT `userID_foreign_key` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=22514871 DEFAULT CHARSET=latin1

And

SELECT @@innodb_buffer_pool_size

Result: 16777216

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?

Rick James · Accepted Answer · 2017-09-10 20:55:59Z

1

There is no problem. Your index is fine. To explain...

The 5730138 in EXPLAIN is an estimate. It can be larger or smaller than the actual value, sometimes by a large amount. Do not be bothered by it.

You have 2.8M of screenshots in that date range, correct? Well, it could take 15 seconds to scan the index to count that many rows.

If you would like further analysis, please provide:
RAM size
innodb_buffer_pool_size
SHOW CREATE TABLE screenshot; (this will show the Engine)
How big the table is (GB)
What type of disk you have (spinning versus SSD)

With those, we can discuss further the impact of caching and I/O and engine. And it may help explain the "15 seconds" versus "20".

(And, yes, use COUNT(*), not COUNT(x) unless you need to test x for NULL.)

If you are using InnoDB, then INDEX(DateTaken, id) is identical to INDEX(DateTaken), so I suggest you were hasty at accepting that answer.

Buffer pool

innodb_buffer_pool_size should be set to about 70% of RAM. What you have is so tiny (the old 16M default), that not even the suggested index can fit in cache. Hence, the query will always be hitting the disk, at least some of the time. Increasing the buffer pool should significantly improve the speed, perhaps down to 2 seconds.

edited Sep 10, 2017 at 20:55

answered Sep 10, 2017 at 17:12

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Michael Tolentino Over a year ago

What is the difference between COUNT(*) and COUNT(x)? I just think that COUNT(x) is more faster, but I need to know why? Your help is greatly appreciated. :)

Rick James Over a year ago

COUNT(x) has the extra overhead of checking x IS NOT NULL before increment the counter by 1. So, in almost all applications, COUNT(*) is better. Your timings to the contrary could be a fluke.

ScaisEdge · Accepted Answer · 2017-09-10 16:46:26Z

0

You could try adding a composite index

  create index test on screenshot (DateTaken, id)

answered Sep 10, 2017 at 16:46

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

John Michael Tolentino Over a year ago

thanks for the help. The speed has improved but the scanning row result is still the same. Do you know the reason why? :)

Bill Karwin Over a year ago

InnoDB indexes always include the primary key. Notice the OP's EXPLAIN output shows "Using index" which shows that even with the single-column index on just DateTaken. If it was marginally faster after following your suggestion, I suggest that was due to the index being fully loaded in the buffer pool.

Gordon Linoff · Accepted Answer · 2017-09-10 16:45:02Z

0

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.

answered Sep 10, 2017 at 16:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

John Michael Tolentino Over a year ago

I got a runtime of 20 seconds.

Collectives™ on Stack Overflow

Index on date field mysql

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related