MySQL 8 Cookbook

Partition pruning

Take the example of the employees table, which is partitioned based on emp_no:

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 RANGE (YEAR(hire_date))(PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB, PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB, PARTITION p2020 VALUES ...