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

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

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