Advanced MySQL 8

An overview of partitioning in MySQL 8.0

When partitioning, it is critical to find a partition key. It is important to make sure that the searches we are going to do in a table go to the correct partition or group of partitions. This means that all commands, for example SELECT, UPDATE, DELETE, must include this column in the WHERE clause to ensure efficiency in the use of these partitions.

Generally, the best practice is to add the partition key to the primary key with auto incrementation enabled, that is, PRIMARY KEY (customer ID example, ID). If you do not have well-designed and small columns for this composite primary key, it could augment all your secondary indexes for no reason.

You can partition by range or hash. The former is excellent ...