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

RANGE partitioning

If you want to partition the employees table based on emp_no and you want to keep 100,000 employees in one partition, you can create it like this:

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`),  KEY `name` (`first_name`,`last_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY RANGE (emp_no)(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (400000) ...

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