MySQL Partitioning and Unix Timestamp

5
votes

I've just started reading on MySQL partitions, they kind of look too good to be true, please bear with me.

I have a table which I would like to partition (which I hope would bring better performance). This is the case / question:

We have a column which stores Unix timestamp values, is it possible to partition the table in that way, that based on the unix timestamp the partitions are separated on a single date? Or do I have to use range based partitioning by defining the ranges before?

Cheers

1
mysql
What is the type of the field that stores the timestamp (INT or TIMESTAMP)? - user672739
The column is of type INT(11) - Flakron Bytyqi

1 Answers

7
votes

You can do whatever you feel like, See: http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html

And example of partitioning by unix_timestamp would be:

ALTER TABLE table1 PARTITION BY KEY myINT11timestamp PARTITIONS 1000;
-- or
ALTER TABLE table1 PARTITION BY HASH (myINT11timestamp/1000) PARTITIONS 10;

Everything you wanted to know about partitions in MySQL 5.5: http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html