mysql主键时间戳,使用时间戳（或日期时间）作为主键（或聚集索引的一部分）的一部分...

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)?

解决方案

Four million rows isn't huge.

A one-byte difference between the data types datetime and timestamp is the last thing you should consider in choosing between those two data types. Review their specs.

Making a timestamp part of your primary key is a bad, bad idea. Think about reviewing what primary key means in a SQL database.

Put an index on your timestamp column. Get an execution plan, and paste that into your question. Determine your median query performance, and paste that into your question, too.

Returning a single day's rows from an indexed, 4 million row table on my desktop computer takes 2ms. (It returns around 8000 rows.)