MySQL: Adding partitions for future dates

Question

I'm trying to partition an existing table (with existing data) using its created field. Is it fine to create many partitions for dates far into the future? Is there any drawback to this?

Since the existing PK of my table is just the id I altered it to include the created field so I can partition it by RANGE:

ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY(id, created);

Add partitions up to the end of 2018:

ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(created))(
    PARTITION p001 VALUES LESS THAN (0),
    PARTITION p002 VALUES LESS THAN (TO_DAYS('2015-05-01')),
    PARTITION p003 VALUES LESS THAN (TO_DAYS('2015-09-01')),
    PARTITION p004 VALUES LESS THAN (TO_DAYS('2016-01-01')),
    PARTITION p005 VALUES LESS THAN (TO_DAYS('2016-05-01')),
    PARTITION p006 VALUES LESS THAN (TO_DAYS('2016-09-01')),
    PARTITION p007 VALUES LESS THAN (TO_DAYS('2017-01-01')),
    PARTITION p008 VALUES LESS THAN (TO_DAYS('2017-05-01')),
    PARTITION p009 VALUES LESS THAN (TO_DAYS('2017-09-01')),
    PARTITION p010 VALUES LESS THAN (TO_DAYS('2018-01-01')),
    PARTITION p011 VALUES LESS THAN (TO_DAYS('2018-05-01')),
    PARTITION p012 VALUES LESS THAN (TO_DAYS('2018-09-01')),
    PARTITION p013 VALUES LESS THAN (TO_DAYS('2019-01-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
)

Is this ok? Or is it much better to wait until the end of the year before applying new partitions for the next year?

Answer 1

• What advantage do you expect to gain by adding partitions? I ask because there is no performance gain to be had, at least not without other changes.

• You need to include the "partition key", created, in all PRIMARY and UNIQUE keys. Usually it is best to put it at the end. (You did that.)

• Since many operations open all partitions (yeah, this is probably a 'bug'), it is inefficient to have lots of 'future' partitions.

• I recommend 20-50 partitions in a table. Less is rather useless; more leads to other inefficiencies.

In my Partition Maintenance blog I list the only 4 use cases for Partitioning, plus discuss how to purge old partitions and when to add new ones.