I have a query of the following form:
SELECT * FROM MyTable WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime]
I would like to optimize this query, and I am thinking about putting an index on timestamp, but am not sure if this would help. Ideally I would like to make timestamp a clustered index, but MySQL does not support clustered indexes, except for primary keys.
MyTable
has 4 million+ rows.Timestamp
is actually of typeINT
.- Once a row has been inserted, it is never changed.
- The number of rows with any given
Timestamp
is on average about 20, but could be as high as 200. - Newly inserted rows have a
Timestamp
that is greater than most of the existing rows, but could be less than some of the more recent rows.
Would an index on Timestamp
help me to optimize this query?
PRIMARY KEY
or aUNIQUE KEY
. If you haven't defined any of these, it will make an "undercover" one, (using a 6-byte INT, if I remember well). – ypercubeᵀᴹ