MySQL 8 InnoDB - Partition function not allowed-CodePudding

I am trying to partition a table with 300M rows. Data consists out of 15 months where I only need the last 6 months on a regular basis and once in a while the last 13 months.

To increase the query speed for the 6 month query I came up with the following partition command:

ALTER TABLE my_db.my_table
 PARTITION BY RANGE (DATE_FORMAT(date, '%Y%m')) (
    PARTITION 2021_H1 VALUES LESS THAN (202107),
    PARTITION 2021_H2 VALUES LESS THAN (202201),
    PARTITION 2022_H1 VALUES LESS THAN (202207),
    PARTITION current VALUES LESS THAN (MAXVALUE)
 ) ;

In return I get:

[HY000][1564] This partition function is not allowed

What's the issue here?

CodePudding user response：

Just as the error message implies, not every function is allowed as partition function. Try using to_days:

ALTER TABLE my_db.my_table
 PARTITION BY RANGE (to_days(`date`)) (
    PARTITION 2021_H1 VALUES LESS THAN (to_days('2021-07-01')),
    PARTITION 2021_H2 VALUES LESS THAN (to_days('2022-01-01')),
    PARTITION 2022_H1 VALUES LESS THAN (to_days('2022-07-01')),
    PARTITION current VALUES LESS THAN (MAXVALUE)
 ) ;

Note:Should you come across Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function, make sure you make a compound primary key based on your original PK and the date column. That's mandatory if you have a PK in your table when defining partions in MySQL.
Caution: Foreign keys are NOT supported for partitioned InnoDB tables. So please make sure you do not have them in the first place before making an partitioning attempt.

CodePudding user response：

ALTER TABLE my_db.my_table
 PARTITION BY RANGE (`date`) (
    PARTITION 2021_H1 VALUES LESS THAN (20210701),
    PARTITION 2021_H2 VALUES LESS THAN (20220101),
    PARTITION 2022_H1 VALUES LESS THAN (20220701),
    PARTITION current VALUES LESS THAN (MAXVALUE)
 ) ;