ALTER TABLE - Partition administration clauses

These clauses of the MySQL statement, ALTER TABLE are used to change the properties of MySQL tables that are partitioned.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
ANALYZE PARTITION partition | 
CHECK PARTITION partition | 
OPTIMIZE PARTITION partition |
REBUILD PARTITION partition | 
REPAIR PARTITION partition

Explanation

Because the MySQL statements, ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE do not work with partitioned tables, you will have to use these clauses of ALTER TABLE instead. They all follow the same syntax format: the clause is followed by a comma-separated list of partitions to be administered.

Examples

The ANALYZE PARTITION clause of the MySQL statement, ALTER TABLE may be used to read and store the indexes of a partition.

ALTER TABLE clients
ANALYZE PARTITION p0, p1, p2;

To check a partion in a MySQL table for corrupted data and indexes, use the CHECK PARTITION clause:

ALTER TABLE clients
CHECK PARTITION p0, p1, p2;

Use the OPTIMIZE PARTITION clause to compact a partition of a MySQL table in which the data has changed significantly.

ALTER TABLE clients
OPTIMIZE PARTITION p0, p1, p2;

The REBUILD PARTITION clause defragments the given partitions of a MySQL table.

ALTER TABLE clients
REBUILD PARTITION p0, p1, p2;

The REPAIR PARTITION clause attempts to repair corrupted partitions, similar to the MySQL statement, REPAIR TABLE for tables.

ALTER TABLE clients
REPAIR PARTITION p0, p1, p2;

See our MySQL documentation of the statement, CREATE TABLE explanation for more information about MySQL table partitioning.