Partition management

ADD partitions

To add a new partition, execute the ADD PARTITION (<PARTITION DEFINITION>) statement:

CREATE TABLE `event_history` (
  `event_id` int(11) NOT NULL,
  `event_name` date NOT NULL,
  `created_at` datetime NOT NULL,
  `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `event_type` varchar(10) NOT NULL,
  `msg` tinytext NOT NULL,
  PRIMARY KEY (`event_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (to_days(created_at))
(PARTITION p20170930 VALUES LESS THAN (736967) ENGINE = InnoDB,
PARTITION p20171001 VALUES LESS THAN (736968) ENGINE = InnoDB,
PARTITION p20171002 VALUES LESS THAN (736969) ENGINE = InnoDB,
PARTITION p20171003 VALUES LESS THAN (736970) ENGINE = InnoDB,
PARTITION p20171004 VALUES LESS THAN (736971) ENGINE = InnoDB,
PARTITION p20171005 VALUES LESS THAN (736972) ENGINE = InnoDB,
PARTITION p20171006 VALUES LESS THAN (736973) ENGINE = InnoDB,
PARTITION p20171007 VALUES LESS THAN (736974) ENGINE = InnoDB,
PARTITION p20171008 VALUES LESS THAN (736975) ENGINE = InnoDB,
PARTITION p20171009 VALUES LESS THAN (736976) ENGINE = InnoDB,
PARTITION p20171010 VALUES LESS THAN (736977) ENGINE = InnoDB,
PARTITION p20171011 VALUES LESS THAN (736978) ENGINE = InnoDB,
PARTITION p20171012 VALUES LESS THAN (736979) ENGINE = InnoDB,
PARTITION p20171013 VALUES LESS THAN (736980) ENGINE = InnoDB,
PARTITION p20171014 VALUES LESS THAN (736981) ENGINE = InnoDB,
PARTITION p20171015 VALUES LESS THAN (736982) ENGINE = InnoDB
);

ALTER TABLE event_history ADD PARTITION (
PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,
PARTITION p20171017 VALUES LESS THAN (736984) ENGINE = InnoDB
);

This statement locks the whole table for a very short time.

Reorganizing partitions

If the MAXVALUE partition is there, you cannot add a partition after MAXVALUE; in that case, you need to the REORGANIZE MAXVALUE partition into two partitions:

ALTER TABLE event_history REORGANIZE PARTITION pmax INTO (PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

ERROR 1507 (HY000): Error in list of partitions to REORGANIZE

Remember MySQL has to substantially move the data while reorganizing partitions and the table will be locked during that period.

You can also reorganize multiple partitions into a single partition:

ALTER TABLE event_history REORGANIZE PARTITION p20171001,p20171002,p20171003,p20171004,p20171005,p20171006,p20171007
INTO (PARTITION p2017_oct_week1 VALUES LESS THAN (736974));

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

DROP partitions

If the data has crossed the retention, you can DROP the whole partition, which is superquick compared with conventional DELETE FROM TABLE statement. This is very helpful in archiving the data efficiently.

ALTER TABLE event_history DROP PARTITION p20170930;

Dropping the partition removes the PARTITION DEFINITION from the table.

TRUNCATE partitions

If you wish to keep PARTITION DEFINITION in the table and remove only the data, you can execute the TRUNCATE PARTITION command:

ALTER TABLE event_history TRUNCATE PARTITION p20171008;

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:

DROP TABLE employees;

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=utf8mb4
PARTITION 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 partitions you want to reduce, that is, 8-6=2:

ALTER TABLE employees COALESCE PARTITION 2;

To increase the partitions from 6 to 16, you can execute the ADD PARTITION statement and specify the number of partitions you want to increase, that is, 16-6=10:

ALTER TABLE employees ADD PARTITION PARTITIONS 10;

Other operations

You can also perform other operations, such as REBUILD, OPTIMIZE, ANALYZE, and REPAIR statements, for a particular partition, for example:

ALTER TABLE event_history REPAIR PARTITION p20171009, p20171010;

+-----------------------+--------+----------+----------+
| Table                 | Op     | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| archive.event_history | repair | status   | OK       |
+-----------------------+--------+----------+----------+
1 row in set (0.00 sec)