Creating an index on a timestamp to optimize query

mysql optimization indexing timestamp
DanielGibbs picture DanielGibbs · Jan 31, 2012 · Viewed 55k times · Source

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 type INT.
  • 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?

Answer

Chris Nash picture Chris Nash · Jan 31, 2012

No question about it. Without the index, your query has to look at every row in the table. With the index, the query will be pretty much instantaneous as far as locating the right rows goes. The price you'll pay is a slight performance decrease in inserts; but that really will be slight.

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Storing JSON in database vs. having a new column for each key

I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format. uid | meta -------------------------------------------------- 1 | {name:[…

Read More