Restrictions and limitations in partitioning

In this section, you will see the restrictions and limitations in MySQL 8 partitioning, covering prohibited constructs, performance considerations, and limitation aspects related to storage engines and functions in detail, to gain optimum benefits from the table partitioning.

Partitioning keys, primary keys, and unique keys

The relationship between partitioning keys with primary keys and unique keys is very important for partition schema structure design. To say the rule in one line it will be that All the columns used in the partitioning in the partition table must include every unique key of the table. So every unique key, including the primary key column on the table, must be part of the partitioning expression. Take a look at the following example for the CREATE TABLE statement using a unique key that does not adhere to the rule:

CREATE TABLE tk1 (
    cl1 INT NOT NULL,
    cl2 DATE NOT NULL,
    cl3 INT NOT NULL,
    cl4 INT NOT NULL,
    UNIQUE...