O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required