MySQL 8 Cookbook

Managing HASH and KEY partitions

The operations performed on HASH and KEY partitions are quite different. You can only reduce or increase the number of partitions.

Suppose the employees table is partitioned based on HASH:

mysql> CREATE TABLE `employees` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` enum('M','F') NOT NULL,  `hire_date` date NOT NULL,  `address` varchar(100) DEFAULT NULL,  PRIMARY KEY (`emp_no`,`hire_date`),  KEY `name` (`first_name`,`last_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY HASH(YEAR(hire_date))PARTITIONS 8;

To reduce the partitions from 8 to 6, you can execute the COALESCE PARTITION statement and specify the number of ...