Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use following query frequently:

SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp

I would like to optimize this query, and I am thinking about put timestamp as part of primary key for clustered index, I think if timestamp is part of primary key , data inserted in table has write to disk sequentially by timestamp field.Also I think this improve my query a lot, but am not sure if this would help.

table has 3-4 million+ rows.
timestamp field never changed.
I use mysql 5.6.11

Anothet point is : if this is improve my query , it is better to use timestamp(4 byte in mysql 5.6) or datetime(5 byte in mysql 5.6)?

share|improve this question

2 Answers 2

Consider the following:

  • An ideal index for this query would be KEY (publish, type, Timestamp). By your choice of query I get that filtering by publish and type is common.
  • If the above is correct, then data inserted into table will not be sequential by Timestamp anyhow
  • A PRIMARY KEY is unique. Are you certain you are not allowed to have two identical Timestamp values? Remember that resolution is 1 second, so it is very common to have same TIMESTMAP values for different rows.
  • I usually like an AUTO_INCREMENT columns as PRIMARY KEY (see Reasons to use AUTO_INCREMENT columns on InnoDB). Just use a normal index on (publish, type, Timestamp) is my general opinion.
share|improve this answer
  1. Why didn't you provide the table structure and query explain plan ? Not all of us have a crystal ball :)

  2. I believe that the publish and type columns don't have a great cardinality (number of distinct values), so you don't have an index on them. That will leave the requirement that the column Timestamp should be indexed in some way.

  3. If you have an index on Timestamp, there's no need to include this column in the PK.

  4. The idea of a clustered index, with Timestamp in PK to achieve data write sequential mode is nice, but it will increase the insert queries response time.

  5. Please read this post about Timestamp VS Datetime: http://stackoverflow.com/questions/409286/datetime-vs-timestamp

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.