MySQL: adding sections for future dates

Question

I am trying to split an existing table (with existing data) using the created field. Is it possible to create many sections for dates far in the future? Is there a flaw in this?

Since the existing PK of my table is just an id , I changed it to include the created field so that I could split it into RANGE :

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

Add sections before 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 ) 

This is normal? Or is it better to wait until the end of the year before applying the new sections for next year?

Answer 1

• What benefit do you expect by adding sections? I ask because there is no performance benefit, at least without other changes.

• You need to include the "partition key", created , in all PRIMARY and UNIQUE . It is usually best to end it. (You did it.)

• Since many operations open all partitions (yes, this is probably a “mistake”), it is inefficient to have many “future” partitions.

• I recommend 20-50 sections in the table. Less is useless; more leads to other inefficiencies.

In my section maintenance block I list only 4 use cases for Partitioning, and also discuss how to clear old sections and when to add new ones.