MySQL paritioning by YEAR: date vs timestamp

Question

I've got a huge table that I'm about to partition. It has a compound 4-element primary key. One of its element is record time, which is a good candidate to be the paritioning key. MySQL manual gives lots of examples where DATE field with RANGE YEAR is a very efficient partitioning key.

My problem is that I have timestamp instead of date. MySQL docs state that it is possible to use timestamp:

Beginning with MySQL 5.1.43, it is also possible to partition a table by RANGE based on the value of a TIMESTAMP column, using the UNIX_TIMESTAMP() function

but it doesn't say anything about performance. The question is: which of date, timestamp is going to be faster as a partitioning key? If DATE is better, I'm considering changing the primary key. I'm gonna divide all data across one-year partitions or half-year partitions.

Andrei Andrei · Accepted Answer · 2014-04-16T14:45:43

The only way to know for sure it to see a benchmark.

I never did one so my answer could be wrong.

In my opinion, the DATE should be faster than the TIMESTAMP since it stores less information so it would be easier to work with. Also since it was the first to be supported for partitioning should tell you that it could be better implemented (not saying that the TIMESTAMP version would be badly implemented).

But my question to you would be if DATE would be a good replacement for TIMESTAMP since you will lose a lot of precision.

MySQL paritioning by YEAR: date vs timestamp

2 Answers