Mysql – Use timestamp(or datetime) as part of primary key (or part of clustered index)

clustered-indexdatabase-designMySQLoptimizationprimary-key

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

Best Answer

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.

Related Solutions

Mysql – Primary key index with a DATETIME as first part of the compound key is never used

This is a bug in 5.5.x. See here

That suggests that your query should be

SELECT * 
FROM `stats`  
WHERE 
   dateDim = CAST('2014-04-03 00:00:00' as datetime)
   AND accountDim = 4
   AND execCodeDim = 9
   AND operationTypeDim = 1
   AND junkDim = 5
   AND ipCountryDim = 3

Sql-server – SQL Server Primary key / clustered index design decision

You are correct to separate "clustered index" from "primary key":

A clustered index is the organisation of data on disk is better if
- narrow
- numeric
- increasing (strictly monotonic)
The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

ProductHistoryID
ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

clustered index should be on ProductHistoryID
unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"

Best Answer

Related Solutions

Mysql – Primary key index with a DATETIME as first part of the compound key is never used

Sql-server – SQL Server Primary key / clustered index design decision

Related Question