18.3.3 Maintenance of Partitions

A number of table and partition maintenance tasks can be carried out using SQL statements intended for such purposes on partitioned tables in MySQL 5.1.

Table maintenance of partitioned tables can be accomplished using the statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE, which are supported for partitioned tables as of MySQL 5.1.27.

Also beginning with MySQL 5.1.27, you can use a number of extensions to ALTER TABLE for performing operations of this type on one or more partitions directly, as described in the following list:

Each of the statements in the list just shown also supports the keyword ALL in place of the list of partition names. Using ALL causes the statement to act on all partitions in the table.

Note

The statements ALTER TABLE ... ANALYZE PARTITION, ALTER TABLE ... CHECK PARTITION, ALTER TABLE ... OPTIMIZE PARTITION, and ALTER TABLE ... REPAIR PARTITION were originally introduced in MySQL 5.1.5, but did not work properly and were disabled in MySQL 5.1.24. They were re-introduced in MySQL 5.1.27. (Bug #20129) The use of these partitioning-specific ALTER TABLE statements with tables which are not partitioned is not supported; beginning with MySQL 5.1.31, it is expressly not permitted. (Bug #39434)

ALTER TABLE ... REBUILD PARTITION was also introduced in MySQL 5.1.5.

The use of mysqlcheck and myisamchk is not supported with partitioned tables.

ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE operations are not supported for subpartitions.