How to partition a table by timestamp, where data from every i'th day goes to partition i?

Question

I'm looking into partitioning a table in my InnoDB database. I have a column corresponding to a UTC timestamp, and I want to partition around that.

The basic idea that I want to capture is a partitioning scheme where, with a fixed number of partitions, we cycle through them as follows:

Given 3 partitions (3 for simplicity)

• Data with timestamp column from day 1 goes into partition 1
• Data with timestamp column from day 2 goes into partition 2
• Data with timestamp column from day 3 goes into partition 3
• Data with timestamp column from day 4 goes into partition 1
• ..... day 5 .... partition 2
• ... rinse and repeat

Basically, extract the day out of the timestamp and put the row into partition DAY MOD N where DAY is the day that the timestamp corresponds to (filtering out hours/minutes/seconds) and N is the number of partitions.

This is probably easily done, but I can't find a similar example to emulate this with. What would the ALTER TABLE query be to partition in this fashion?

Update

By atxdba's suggestion, I tried partitioning by hash. I tried the following statement:

ALTER TABLE table_to_partition PARTITION BY HASH(DAYOFMONTH(FROM_UNIXTIME(my_timestamp))) partitions 8;

This results in error code 1564: This partition function is not allowed. Looking at this list of limitations for partitioning, it doesn't appear that FROM_UNIXTIMESTAMP is supported for partioning a table, so a different transformation from timestamp to date is required.

Answer 1

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression. But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+

Answer 2

You might want to look at hash partitioning. You just define how many "buckets" you want and a column to hash by, mysql takes care of distributing the rows for you.

Answer 3

quasi-related: remember if you have an auto incrementing column in a partitioned table, it will act different depending on the table type:

MyISAM: the auto inc. column will increment for each partition

InnoDB: the auto inc. column will be unique in the table.