Mysql select query between dates takes more execution time

Question

In my test table there are 1 million rows, column id is auto generated number from 1 to one million and id column is unique index, timestore column storing insertion time, when execute select query between id range Ex:

select * from test where id > 345673 and id < 453267

Execution time - 0.379s

if execute select query between two dates

select * from test where timestore between '2014-12-28 16:59:50' and '2014-12-28 17:1:50'

Execution time - 1.478s

Why second query takes more than first ?

I don't know much about this, but taking an educated guess, the conversion between string values and MySQL appropriate timestamp columns takes longer than checking two integers? — Jhecht, Dec 30 '14 at 04:02
@Jhecht maybe, but that only happens once for the whole query — Andrew Barber, Dec 30 '14 at 04:03
Ooh, @dasblinkenlight; I didn't even notice the gt/lt and between — Andrew Barber, Dec 30 '14 at 04:04

score 4 · Answer 1 · answered Dec 30 '14 at 04:15

Assuming your table is indexed by the id column only and not by date. When your first query is executing it can jump directly to the index of ID 345673 skipping everything below it. When your second query is executed the query has to read all of the million rows in the table because it is not indexed.

I would suggest that you create a clustered index using the timestore and ID fields in that order. Also make sure when using the WHERE statement to use the timestore and ID clauses in the same order as the clustered index to ensure that the compiler uses that index.

You can also create non-clustered index from the timestore-id clustered index for only timestore or ID individually.

Mysql select query between dates takes more execution time

1 Answers1