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