Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating an index on a timestamp to optimize query

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?

like image 459
DanielGibbs Avatar asked Jan 31 '12 22:01

DanielGibbs


People also ask

Can we create index on timestamp?

Yes, with the new unique constraint you're good.

Would adding an index improve query time?

Only create one index at a time because not all indexes will decrease query time. PostgreSQL's query planning is pretty efficient, so adding a new index may not affect how fast queries are performed. Adding an index will increase how long it takes your database to fully update after a write operation.

How do you create an index in SQL to improve performance?

In this case, you can create a large number of SQL Server indexes, adding all required columns as index key or non-key columns to enhance the performance of the SELECT queries and get the requested data faster. Another thing to consider when indexing a database table is the size of the table.

Does index improve query performance?

Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement. They aren't a “silver bullet” – they don't always solve performance problems with SELECT statements. However, they can certainly help.


3 Answers

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.

like image 62
Chris Nash Avatar answered Oct 06 '22 10:10

Chris Nash


You should definitely use an index. MySQL has no clue what order those timestamps are in, and in order to find a record for a given timestamp (or timestamp range) it needs to look through every single record. And with 4 million of them, that's quite a bit of time! Indexes are your way of telling MySQL about your data -- "I'm going to look at this field quite often, so keep an list of where I can find the records for each value."

Indexes in general are a good idea for regularly queried fields. The only downside to defining indexes is that they use extra storage space, so unless you're real tight on space, you should try to use them. If they don't apply, MySQL will just ignore them anyway.

like image 45
kitti Avatar answered Oct 06 '22 11:10

kitti


I don't disagree with the importance of indexing to improve select query times, but if you can index on other keys (and form your queries with these indexes), the need to index on timestamp may not be needed.

For example, if you have a table with timestamp, category, and userId, it may be better to create an index on userId instead. In a table with many different users this will reduce considerably the remaining set on which to search the timestamp.

...and If I'm not mistaken, the advantage of this would be to avoid the overhead of creating the timestamp index on each insertion -- in a table with high insertion rates and highly unique timestamps this could be an important consideration.

I'm struggling with the same problems of indexing based on timestamps and other keys. I still have testing to do so I can put proof behind what I say here. I'll try to postback based on my results.

A scenario for better explanation:

  1. timestamp 99% unique
  2. userId 80% unique
  3. category 25% unique

    • Indexing on timestamp will quickly reduce query results to 1% the table size
    • Indexing on userId will quickly reduce query results to 20% the table size
    • Indexing on category will quickly reduce query results to 75% the table size
    • Insertion with indexes on timestamp will have high overhead **
    • Despite our knowledge that our insertions will respect the fact of have incrementing timestamps, I don't see any discussion of MySQL optimisation based on incremental keys.
    • Insertion with indexes on userId will reasonably high overhead.
    • Insertion with indexes on category will have reasonably low overhead.

** I'm sorry, I don't know the calculated overhead or insertion with indexing.

like image 8
blackstrype Avatar answered Oct 06 '22 10:10

blackstrype