22.3 Partition Management

Menu:
partitioning-management-range-list:: Management of RANGE and LIST Partitions
partitioning-management-hash-key:: Management of HASH and KEY Partitions
partitioning-management-exchange:: Exchanging Partitions and Subpartitions with Tables
partitioning-maintenance:: Maintenance of Partitions
partitioning-info:: Obtaining Information About Partitions

MySQL 5.7 provides a number of ways to modify partitioned tables. It is possible to add, drop, redefine, merge, or split existing partitions. All of these actions can be carried out using the partitioning extensions to the *note 'ALTER TABLE': alter-table-partition-operations. statement. There are also ways to obtain information about partitioned tables and partitions. We discuss these topics in the sections that follow.

For information about partition management in tables partitioned by 'RANGE' or 'LIST', see *note partitioning-management-range-list::.
For a discussion of managing 'HASH' and 'KEY' partitions, see *note partitioning-management-hash-key::.
See *note partitioning-info::, for a discussion of mechanisms provided in MySQL 5.7 for obtaining information about partitioned tables and partitions.
For a discussion of performing maintenance operations on partitions, see *note partitioning-maintenance::.

Note:

In MySQL 5.7, all partitions of a partitioned table must have the same number of subpartitions, and it is not possible to change the subpartitioning once the table has been created.

To change a table's partitioning scheme, it is necessary only to use the note 'ALTER TABLE': alter-table-partition-operations. statement with a PARTITION_OPTIONS clause. This clause has the same syntax as that as used with note 'CREATE TABLE': create-table. for creating a partitioned table, and always begins with the keywords 'PARTITION BY'. Suppose that you have a table partitioned by range using the following *note 'CREATE TABLE': create-table. statement:

 CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
     PARTITION BY RANGE( YEAR(purchased) ) (
         PARTITION p0 VALUES LESS THAN (1990),
         PARTITION p1 VALUES LESS THAN (1995),
         PARTITION p2 VALUES LESS THAN (2000),
         PARTITION p3 VALUES LESS THAN (2005)
     );

To repartition this table so that it is partitioned by key into two partitions using the 'id' column value as the basis for the key, you can use this statement:

 ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

This has the same effect on the structure of the table as dropping the table and re-creating it using 'CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;'.

'ALTER TABLE ... ENGINE = ...' changes only the storage engine used by the table, and leaves the table's partitioning scheme intact. Use 'ALTER TABLE ... REMOVE PARTITIONING' to remove a table's partitioning. See *note alter-table::.

Important:

Only a single 'PARTITION BY', 'ADD PARTITION', 'DROP PARTITION', 'REORGANIZE PARTITION', or 'COALESCE PARTITION' clause can be used in a given note 'ALTER TABLE': alter-table-partition-operations. statement. If you (for example) wish to drop a partition and reorganize a table's remaining partitions, you must do so in two separate note 'ALTER TABLE': alter-table-partition-operations. statements (one using 'DROP PARTITION' and then a second one using 'REORGANIZE PARTITION').

In MySQL 5.7, it is possible to delete all rows from one or more selected partitions using *note 'ALTER TABLE ... TRUNCATE PARTITION': alter-table.

File: manual.info.tmp, Node: partitioning-management-range-list, Next: partitioning-management-hash-key, Prev: partitioning-management, Up: partitioning-management

22.3.1 Management of RANGE and LIST Partitions

Adding and dropping of range and list partitions are handled in a similar fashion, so we discuss the management of both sorts of partitioning in this section. For information about working with tables that are partitioned by hash or key, see *note partitioning-management-hash-key::.

Dropping a partition from a table that is partitioned by either 'RANGE' or by 'LIST' can be accomplished using the note 'ALTER TABLE': alter-table-partition-operations. statement with the 'DROP PARTITION' option. Suppose that you have created a table that is partitioned by range and then populated with 10 records using the following note 'CREATE TABLE': create-table. and *note 'INSERT': insert. statements:

 mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
     ->     PARTITION BY RANGE( YEAR(purchased) ) (
     ->         PARTITION p0 VALUES LESS THAN (1990),
     ->         PARTITION p1 VALUES LESS THAN (1995),
     ->         PARTITION p2 VALUES LESS THAN (2000),
     ->         PARTITION p3 VALUES LESS THAN (2005),
     ->         PARTITION p4 VALUES LESS THAN (2010),
     ->         PARTITION p5 VALUES LESS THAN (2015)
     ->     );
 Query OK, 0 rows affected (0.28 sec)

 mysql> INSERT INTO tr VALUES
     ->     (1, 'desk organiser', '2003-10-15'),
     ->     (2, 'alarm clock', '1997-11-05'),
     ->     (3, 'chair', '2009-03-10'),
     ->     (4, 'bookcase', '1989-01-10'),
     ->     (5, 'exercise bike', '2014-05-09'),
     ->     (6, 'sofa', '1987-06-05'),
     ->     (7, 'espresso maker', '2011-11-22'),
     ->     (8, 'aquarium', '1992-08-04'),
     ->     (9, 'study desk', '2006-09-16'),
     ->     (10, 'lava lamp', '1998-12-25');
 Query OK, 10 rows affected (0.05 sec)
 Records: 10  Duplicates: 0  Warnings: 0

You can see which items should have been inserted into partition 'p2' as shown here:

 mysql> SELECT * FROM tr
     ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
 +------+-------------+------------+
 | id   | name        | purchased  |
 +------+-------------+------------+
 |    2 | alarm clock | 1997-11-05 |
 |   10 | lava lamp   | 1998-12-25 |
 +------+-------------+------------+
 2 rows in set (0.00 sec)

You can also get this information using partition selection, as shown here:

 mysql> SELECT * FROM tr PARTITION (p2);
 +------+-------------+------------+
 | id   | name        | purchased  |
 +------+-------------+------------+
 |    2 | alarm clock | 1997-11-05 |
 |   10 | lava lamp   | 1998-12-25 |
 +------+-------------+------------+
 2 rows in set (0.00 sec)

See *note partitioning-selection::, for more information.

To drop the partition named 'p2', execute the following command:

 mysql> ALTER TABLE tr DROP PARTITION p2;
 Query OK, 0 rows affected (0.03 sec)

Note:

The note 'NDBCLUSTER': mysql-cluster. storage engine does not support 'ALTER TABLE ... DROP PARTITION'. It does, however, support the other partitioning-related extensions to note 'ALTER TABLE': alter-table-partition-operations. that are described in this chapter.

It is very important to remember that, when you drop a partition, you also delete all the data that was stored in that partition. You can see that this is the case by re-running the previous *note 'SELECT': select. query:

 mysql> SELECT * FROM tr WHERE purchased
     -> BETWEEN '1995-01-01' AND '1999-12-31';
 Empty set (0.00 sec)

Because of this, you must have the 'DROP' privilege for a table before you can execute 'ALTER TABLE ... DROP PARTITION' on that table.

If you wish to drop all data from all partitions while preserving the table definition and its partitioning scheme, use the note 'TRUNCATE TABLE': truncate-table. statement. (See note truncate-table::.)

If you intend to change the partitioning of a table without losing data, use 'ALTER TABLE ... REORGANIZE PARTITION' instead. See below or in *note alter-table::, for information about 'REORGANIZE PARTITION'.

If you now execute a *note 'SHOW CREATE TABLE': show-create-table. statement, you can see how the partitioning makeup of the table has been changed:

 mysql> SHOW CREATE TABLE tr\G
 *************************** 1. row ***************************
        Table: tr
 Create Table: CREATE TABLE `tr` (
   `id` int(11) DEFAULT NULL,
   `name` varchar(50) DEFAULT NULL,
   `purchased` date DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 /*!50100 PARTITION BY RANGE ( YEAR(purchased))
 (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
  PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
  PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
 1 row in set (0.00 sec)

When you insert new rows into the changed table with 'purchased' column values between ''1995-01-01'' and ''2004-12-31'' inclusive, those rows are stored in partition 'p3'. You can verify this as follows:

 mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
 Query OK, 1 row affected (0.00 sec)

 mysql> SELECT * FROM tr WHERE purchased
     -> BETWEEN '1995-01-01' AND '2004-12-31';
 +------+----------------+------------+
 | id   | name           | purchased  |
 +------+----------------+------------+
 |    1 | desk organiser | 2003-10-15 |
 |   11 | pencil holder  | 1995-07-12 |
 +------+----------------+------------+
 2 rows in set (0.00 sec)

 mysql> ALTER TABLE tr DROP PARTITION p3;
 Query OK, 0 rows affected (0.03 sec)

 mysql> SELECT * FROM tr WHERE purchased
     -> BETWEEN '1995-01-01' AND '2004-12-31';
 Empty set (0.00 sec)

The number of rows dropped from the table as a result of 'ALTER TABLE ... DROP PARTITION' is not reported by the server as it would be by the equivalent *note 'DELETE': delete. query.

Dropping 'LIST' partitions uses exactly the same 'ALTER TABLE ... DROP PARTITION' syntax as used for dropping 'RANGE' partitions. However, there is one important difference in the effect this has on your use of the table afterward: You can no longer insert into the table any rows having any of the values that were included in the value list defining the deleted partition. (See *note partitioning-list::, for an example.)

To add a new range or list partition to a previously partitioned table, use the 'ALTER TABLE ... ADD PARTITION' statement. For tables which are partitioned by 'RANGE', this can be used to add a new range to the end of the list of existing partitions. Suppose that you have a partitioned table containing membership data for your organization, which is defined as follows:

 CREATE TABLE members (
     id INT,
     fname VARCHAR(25),
     lname VARCHAR(25),
     dob DATE
 )
 PARTITION BY RANGE( YEAR(dob) ) (
     PARTITION p0 VALUES LESS THAN (1980),
     PARTITION p1 VALUES LESS THAN (1990),
     PARTITION p2 VALUES LESS THAN (2000)
 );

Suppose further that the minimum age for members is 16. As the calendar approaches the end of 2015, you realize that you are soon going to be admitting members who were born in 2000 (and later). You can modify the 'members' table to accommodate new members born in the years 2000 to 2010 as shown here:

 ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

With tables that are partitioned by range, you can use 'ADD PARTITION' to add new partitions to the high end of the partitions list only. Trying to add a new partition in this manner between or before existing partitions results in an error as shown here:

 mysql> ALTER TABLE members
      >     ADD PARTITION (
      >     PARTITION n VALUES LESS THAN (1970));
 ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
    increasing for each partition

You can work around this problem by reorganizing the first partition into two new ones that split the range between them, like this:

 ALTER TABLE members
     REORGANIZE PARTITION p0 INTO (
         PARTITION n0 VALUES LESS THAN (1970),
         PARTITION n1 VALUES LESS THAN (1980)
 );

Using *note 'SHOW CREATE TABLE': show-create-table. you can see that the 'ALTER TABLE' statement has had the desired effect:

 mysql> SHOW CREATE TABLE members\G
 *************************** 1. row ***************************
        Table: members
 Create Table: CREATE TABLE `members` (
   `id` int(11) DEFAULT NULL,
   `fname` varchar(25) DEFAULT NULL,
   `lname` varchar(25) DEFAULT NULL,
   `dob` date DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 /*!50100 PARTITION BY RANGE ( YEAR(dob))
 (PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
  PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
  PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
  PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
  PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
 1 row in set (0.00 sec)

See also *note alter-table-partition-operations::.

You can also use 'ALTER TABLE ... ADD PARTITION' to add new partitions to a table that is partitioned by 'LIST'. Suppose a table 'tt' is defined using the following *note 'CREATE TABLE': create-table. statement:

 CREATE TABLE tt (
     id INT,
     data INT
 )
 PARTITION BY LIST(data) (
     PARTITION p0 VALUES IN (5, 10, 15),
     PARTITION p1 VALUES IN (6, 12, 18)
 );

You can add a new partition in which to store rows having the 'data' column values '7', '14', and '21' as shown:

 ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

Keep in mind that you cannot add a new 'LIST' partition encompassing any values that are already included in the value list of an existing partition. If you attempt to do so, an error results:

 mysql> ALTER TABLE tt ADD PARTITION
      >     (PARTITION np VALUES IN (4, 8, 12));
 ERROR 1465 (HY000): Multiple definition of same constant »
                     in list partitioning

Because any rows with the 'data' column value '12' have already been assigned to partition 'p1', you cannot create a new partition on table 'tt' that includes '12' in its value list. To accomplish this, you could drop 'p1', and add 'np' and then a new 'p1' with a modified definition. However, as discussed earlier, this would result in the loss of all data stored in 'p1'--and it is often the case that this is not what you really want to do. Another solution might appear to be to make a copy of the table with the new partitioning and to copy the data into it using *note 'CREATE TABLE ... SELECT ...': create-table, then drop the old table and rename the new one, but this could be very time-consuming when dealing with a large amounts of data. This also might not be feasible in situations where high availability is a requirement.

You can add multiple partitions in a single 'ALTER TABLE ... ADD PARTITION' statement as shown here:

 CREATE TABLE employees (
   id INT NOT NULL,
   fname VARCHAR(50) NOT NULL,
   lname VARCHAR(50) NOT NULL,
   hired DATE NOT NULL
 )
 PARTITION BY RANGE( YEAR(hired) ) (
   PARTITION p1 VALUES LESS THAN (1991),
   PARTITION p2 VALUES LESS THAN (1996),
   PARTITION p3 VALUES LESS THAN (2001),
   PARTITION p4 VALUES LESS THAN (2005)
 );

 ALTER TABLE employees ADD PARTITION (
     PARTITION p5 VALUES LESS THAN (2010),
     PARTITION p6 VALUES LESS THAN MAXVALUE
 );

Fortunately, MySQL's partitioning implementation provides ways to redefine partitions without losing data. Let us look first at a couple of simple examples involving 'RANGE' partitioning. Recall the 'members' table which is now defined as shown here:

 mysql> SHOW CREATE TABLE members\G
 *************************** 1. row ***************************
        Table: members
 Create Table: CREATE TABLE `members` (
   `id` int(11) DEFAULT NULL,
   `fname` varchar(25) DEFAULT NULL,
   `lname` varchar(25) DEFAULT NULL,
   `dob` date DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 /*!50100 PARTITION BY RANGE ( YEAR(dob))
 (PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
  PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
  PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
  PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
  PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
 1 row in set (0.00 sec)

Suppose that you would like to move all rows representing members born before 1960 into a separate partition. As we have already seen, this cannot be done using note 'ALTER TABLE ... ADD PARTITION': alter-table-partition-operations. However, you can use another partition-related extension to note 'ALTER TABLE': alter-table-partition-operations. to accomplish this:

 ALTER TABLE members REORGANIZE PARTITION n0 INTO (
     PARTITION s0 VALUES LESS THAN (1960),
     PARTITION s1 VALUES LESS THAN (1970)
 );

In effect, this command splits partition 'n0' into two new partitions 's0' and 's1'. It also moves the data that was stored in 'n0' into the new partitions according to the rules embodied in the two 'PARTITION ... VALUES ...' clauses, so that 's0' contains only those records for which 'YEAR(dob)' is less than 1960 and 's1' contains those rows in which 'YEAR(dob)' is greater than or equal to 1960 but less than 1970.

A 'REORGANIZE PARTITION' clause may also be used for merging adjacent partitions. You can reverse the effect of the previous statement on the 'members' table as shown here:

 ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
     PARTITION p0 VALUES LESS THAN (1970)
 );

No data is lost in splitting or merging partitions using 'REORGANIZE PARTITION'. In executing the above statement, MySQL moves all of the records that were stored in partitions 's0' and 's1' into partition 'p0'.

The general syntax for 'REORGANIZE PARTITION' is shown here:

 ALTER TABLE TBL_NAME
     REORGANIZE PARTITION PARTITION_LIST
     INTO (PARTITION_DEFINITIONS);

Here, TBL_NAME is the name of the partitioned table, and PARTITION_LIST is a comma-separated list of names of one or more existing partitions to be changed. PARTITION_DEFINITIONS is a comma-separated list of new partition definitions, which follow the same rules as for the PARTITION_DEFINITIONS list used in *note 'CREATE TABLE': create-table. You are not limited to merging several partitions into one, or to splitting one partition into many, when using 'REORGANIZE PARTITION'. For example, you can reorganize all four partitions of the 'members' table into two, like this:

 ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
     PARTITION m0 VALUES LESS THAN (1980),
     PARTITION m1 VALUES LESS THAN (2000)
 );

You can also use 'REORGANIZE PARTITION' with tables that are partitioned by 'LIST'. Let us return to the problem of adding a new partition to the list-partitioned 'tt' table and failing because the new partition had a value that was already present in the value-list of one of the existing partitions. We can handle this by adding a partition that contains only nonconflicting values, and then reorganizing the new partition and the existing one so that the value which was stored in the existing one is now moved to the new one:

 ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
 ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
     PARTITION p1 VALUES IN (6, 18),
     PARTITION np VALUES in (4, 8, 12)
 );

Here are some key points to keep in mind when using 'ALTER TABLE ... REORGANIZE PARTITION' to repartition tables that are partitioned by 'RANGE' or 'LIST':

The 'PARTITION' options used to determine the new partitioning scheme are subject to the same rules as those used with a *note 'CREATE TABLE': create-table. statement.

A new 'RANGE' partitioning scheme cannot have any overlapping ranges; a new 'LIST' partitioning scheme cannot have any overlapping sets of values.
The combination of partitions in the PARTITION_DEFINITIONS list should account for the same range or set of values overall as the combined partitions named in the PARTITION_LIST.

For example, partitions 'p1' and 'p2' together cover the years 1980 through 1999 in the 'members' table used as an example in this section. Any reorganization of these two partitions should cover the same range of years overall.
For tables partitioned by 'RANGE', you can reorganize only adjacent partitions; you cannot skip range partitions.

For instance, you could not reorganize the example 'members' table using a statement beginning with 'ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...' because 'p0' covers the years prior to 1970 and 'p2' the years from 1990 through 1999 inclusive, so these are not adjacent partitions. (You cannot skip partition 'p1' in this case.)
You cannot use 'REORGANIZE PARTITION' to change the type of partitioning used by the table (for example, you cannot change 'RANGE' partitions to 'HASH' partitions or the reverse). You also cannot use this statement to change the partitioning expression or column. To accomplish either of these tasks without dropping and re-creating the table, you can use *note 'ALTER TABLE ... PARTITION BY ...': alter-table-partition-operations, as shown here:
```
  ALTER TABLE members
      PARTITION BY HASH( YEAR(dob) )
      PARTITIONS 8;
```

File: manual.info.tmp, Node: partitioning-management-hash-key, Next: partitioning-management-exchange, Prev: partitioning-management-range-list, Up: partitioning-management

22.3.2 Management of HASH and KEY Partitions

Tables which are partitioned by hash or by key are very similar to one another with regard to making changes in a partitioning setup, and both differ in a number of ways from tables which have been partitioned by range or list. For that reason, this section addresses the modification of tables partitioned by hash or by key only. For a discussion of adding and dropping of partitions of tables that are partitioned by range or list, see *note partitioning-management-range-list::.

You cannot drop partitions from tables that are partitioned by 'HASH' or 'KEY' in the same way that you can from tables that are partitioned by 'RANGE' or 'LIST'. However, you can merge 'HASH' or 'KEY' partitions using the 'ALTER TABLE ... COALESCE PARTITION' statement. Suppose that you have a table containing data about clients, which is divided into twelve partitions. The 'clients' table is defined as shown here:

 CREATE TABLE clients (
     id INT,
     fname VARCHAR(30),
     lname VARCHAR(30),
     signed DATE
 )
 PARTITION BY HASH( MONTH(signed) )
 PARTITIONS 12;

To reduce the number of partitions from twelve to eight, execute the following *note 'ALTER TABLE': alter-table-partition-operations. command:

 mysql> ALTER TABLE clients COALESCE PARTITION 4;
 Query OK, 0 rows affected (0.02 sec)

'COALESCE' works equally well with tables that are partitioned by 'HASH', 'KEY', 'LINEAR HASH', or 'LINEAR KEY'. Here is an example similar to the previous one, differing only in that the table is partitioned by 'LINEAR KEY':

 mysql> CREATE TABLE clients_lk (
     ->     id INT,
     ->     fname VARCHAR(30),
     ->     lname VARCHAR(30),
     ->     signed DATE
     -> )
     -> PARTITION BY LINEAR KEY(signed)
     -> PARTITIONS 12;
 Query OK, 0 rows affected (0.03 sec)

 mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
 Query OK, 0 rows affected (0.06 sec)
 Records: 0  Duplicates: 0  Warnings: 0

The number following 'COALESCE PARTITION' is the number of partitions to merge into the remainder--in other words, it is the number of partitions to remove from the table.

If you attempt to remove more partitions than the table has, the result is an error like the one shown:

 mysql> ALTER TABLE clients COALESCE PARTITION 18;
 ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

To increase the number of partitions for the 'clients' table from 12 to 18. use 'ALTER TABLE ... ADD PARTITION' as shown here:

 ALTER TABLE clients ADD PARTITION PARTITIONS 6;

File: manual.info.tmp, Node: partitioning-management-exchange, Next: partitioning-maintenance, Prev: partitioning-management-hash-key, Up: partitioning-management

22.3.3 Exchanging Partitions and Subpartitions with Tables

In MySQL 5.7, it is possible to exchange a table partition or subpartition with a table using 'ALTER TABLE PT EXCHANGE PARTITION P WITH TABLE NT', where PT is the partitioned table and P is the partition or subpartition of PT to be exchanged with unpartitioned table NT, provided that the following statements are true:

Table NT is not itself partitioned.
Table NT is not a temporary table.
The structures of tables PT and NT are otherwise identical.
Table 'nt' contains no foreign key references, and no other table has any foreign keys that refer to 'nt'.
There are no rows in NT that lie outside the boundaries of the partition definition for P. This condition does not apply if the 'WITHOUT VALIDATION' option is used. The '[{WITH|WITHOUT} VALIDATION]' option was added in MySQL 5.7.5.
Both tables must use the same character set and collation.
For 'InnoDB' tables, both tables must use the same row format. To determine the row format of an 'InnoDB' table, query the Information Schema *note 'INNODB_SYS_TABLES': information-schema-innodb-sys-tables-table. table.
Any partition-level 'MAX_ROWS' setting for 'p' must be the same as the table-level 'MAX_ROWS' value set for 'nt'. The setting for any partition-level 'MIN_ROWS' setting for 'p' must also be the same any table-level 'MIN_ROWS' value set for 'nt'.

This is true in either case whether not 'pt' has an exlpicit table-level 'MAX_ROWS' or 'MIN_ROWS' option in effect.
The 'AVG_ROW_LENGTH' cannot differ between the two tables 'pt' and 'nt'.
'pt' does not have any partitions that use the 'DATA DIRECTORY' option. This restriction is lifted for 'InnoDB' tables in MySQL 5.7.25 and later.
'INDEX DIRECTORY' cannot differ between the table and the partition to be exchanged with it.
No table or partition 'TABLESPACE' options can be used in either of the tables.

In addition to the 'ALTER', 'INSERT', and 'CREATE' privileges usually required for note 'ALTER TABLE': alter-table. statements, you must have the 'DROP' privilege to perform note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table.

You should also be aware of the following effects of *note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table.:

Executing *note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table. does not invoke any triggers on either the partitioned table or the table to be exchanged.
Any 'AUTO_INCREMENT' columns in the exchanged table are reset.
The 'IGNORE' keyword has no effect when used with 'ALTER TABLE ... EXCHANGE PARTITION'.

The syntax of the *note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table. statement is shown here, where PT is the partitioned table, P is the partition or subpartition to be exchanged, and NT is the nonpartitioned table to be exchanged with P:

 ALTER TABLE PT
     EXCHANGE PARTITION P
     WITH TABLE NT;

Optionally, you can append a 'WITH VALIDATION' or 'WITHOUT VALIDATION' clause. When 'WITHOUT VALIDATION' is specified, the *note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table. operation does not perform row-by-row validation when exchanging a partition a nonpartitioned table, allowing database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition. 'WITH VALIDATION' is the default behavior and need not be specified explicitly. The '[{WITH|WITHOUT} VALIDATION]' option was added in MySQL 5.7.5.

One and only one partition or subpartition may be exchanged with one and only one nonpartitioned table in a single note 'ALTER TABLE EXCHANGE PARTITION': alter-table. statement. To exchange multiple partitions or subpartitions, use multiple note 'ALTER TABLE EXCHANGE PARTITION': alter-table. statements. 'EXCHANGE PARTITION' may not be combined with other *note 'ALTER TABLE': alter-table. options. The partitioning and (if applicable) subpartitioning used by the partitioned table may be of any type or types supported in MySQL 5.7.

Exchanging a Partition with a Nonpartitioned Table

Suppose that a partitioned table 'e' has been created and populated using the following SQL statements:

 CREATE TABLE e (
     id INT NOT NULL,
     fname VARCHAR(30),
     lname VARCHAR(30)
 )
     PARTITION BY RANGE (id) (
         PARTITION p0 VALUES LESS THAN (50),
         PARTITION p1 VALUES LESS THAN (100),
         PARTITION p2 VALUES LESS THAN (150),
         PARTITION p3 VALUES LESS THAN (MAXVALUE)
 );

 INSERT INTO e VALUES
     (1669, "Jim", "Smith"),
     (337, "Mary", "Jones"),
     (16, "Frank", "White"),
     (2005, "Linda", "Black");

Now we create a nonpartitioned copy of 'e' named 'e2'. This can be done using the *note 'mysql': mysql. client as shown here:

 mysql> CREATE TABLE e2 LIKE e;
 Query OK, 0 rows affected (1.34 sec)

 mysql> ALTER TABLE e2 REMOVE PARTITIONING;
 Query OK, 0 rows affected (0.90 sec)
 Records: 0  Duplicates: 0  Warnings: 0

You can see which partitions in table 'e' contain rows by querying the Information Schema *note 'PARTITIONS': information-schema-partitions-table. table, like this:

 mysql> SELECT PARTITION_NAME, TABLE_ROWS
     ->     FROM INFORMATION_SCHEMA.PARTITIONS
     ->     WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |          1 |
 | p1             |          0 |
 | p2             |          0 |
 | p3             |          3 |
 +----------------+------------+
 4 rows in set (0.00 sec)

Note:

For partitioned 'InnoDB' tables, the row count given in the 'TABLE_ROWS' column of the Information Schema *note 'PARTITIONS': information-schema-partitions-table. table is only an estimated value used in SQL optimization, and is not always exact.

To exchange partition 'p0' in table 'e' with table 'e2', you can use the *note 'ALTER TABLE': alter-table-partition-operations. statement shown here:

 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
 Query OK, 0 rows affected (0.28 sec)

More precisely, the statement just issued causes any rows found in the partition to be swapped with those found in the table. You can observe how this has happened by querying the Information Schema *note 'PARTITIONS': information-schema-partitions-table. table, as before. The table row that was previously found in partition 'p0' is no longer present:

 mysql> SELECT PARTITION_NAME, TABLE_ROWS
     ->     FROM INFORMATION_SCHEMA.PARTITIONS
     ->     WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |          0 |
 | p1             |          0 |
 | p2             |          0 |
 | p3             |          3 |
 +----------------+------------+
 4 rows in set (0.00 sec)

If you query table 'e2', you can see that the 'missing' row can now be found there:

 mysql> SELECT * FROM e2;
 +----+-------+-------+
 | id | fname | lname |
 +----+-------+-------+
 | 16 | Frank | White |
 +----+-------+-------+
 1 row in set (0.00 sec)

The table to be exchanged with the partition does not necessarily have to be empty. To demonstrate this, we first insert a new row into table 'e', making sure that this row is stored in partition 'p0' by choosing an 'id' column value that is less than 50, and verifying this afterward by querying the *note 'PARTITIONS': information-schema-partitions-table. table:

 mysql> INSERT INTO e VALUES (41, "Michael", "Green");
 Query OK, 1 row affected (0.05 sec)

 mysql> SELECT PARTITION_NAME, TABLE_ROWS
     ->     FROM INFORMATION_SCHEMA.PARTITIONS
     ->     WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |          1 |
 | p1             |          0 |
 | p2             |          0 |
 | p3             |          3 |
 +----------------+------------+
 4 rows in set (0.00 sec)

Now we once again exchange partition 'p0' with table 'e2' using the same *note 'ALTER TABLE': alter-table-partition-operations. statement as previously:

 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
 Query OK, 0 rows affected (0.28 sec)

The output of the following queries shows that the table row that was stored in partition 'p0' and the table row that was stored in table 'e2', prior to issuing the *note 'ALTER TABLE': alter-table-partition-operations. statement, have now switched places:

 mysql> SELECT * FROM e;
 +------+-------+-------+
 | id   | fname | lname |
 +------+-------+-------+
 |   16 | Frank | White |
 | 1669 | Jim   | Smith |
 |  337 | Mary  | Jones |
 | 2005 | Linda | Black |
 +------+-------+-------+
 4 rows in set (0.00 sec)

 mysql> SELECT PARTITION_NAME, TABLE_ROWS
     ->     FROM INFORMATION_SCHEMA.PARTITIONS
     ->     WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |          1 |
 | p1             |          0 |
 | p2             |          0 |
 | p3             |          3 |
 +----------------+------------+
 4 rows in set (0.00 sec)

 mysql> SELECT * FROM e2;
 +----+---------+-------+
 | id | fname   | lname |
 +----+---------+-------+
 | 41 | Michael | Green |
 +----+---------+-------+
 1 row in set (0.00 sec)

Nonmatching Rows

You should keep in mind that any rows found in the nonpartitioned table prior to issuing the *note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table. statement must meet the conditions required for them to be stored in the target partition; otherwise, the statement fails. To see how this occurs, first insert a row into 'e2' that is outside the boundaries of the partition definition for partition 'p0' of table 'e'. For example, insert a row with an 'id' column value that is too large; then, try to exchange the table with the partition again:

 mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
 Query OK, 1 row affected (0.08 sec)

 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
 ERROR 1707 (HY000): Found row that does not match the partition

Only the 'WITHOUT VALIDATION' option would permit this operation to succeed:

 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
 Query OK, 0 rows affected (0.02 sec)

When a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using note 'REPAIR TABLE': repair-table. or note 'ALTER TABLE ... REPAIR PARTITION': alter-table-partition-operations.

Exchanging Partitions Without Row-By-Row Validation

To avoid time consuming validation when exchanging a partition with a table that has many rows, it is possible to skip the row-by-row validation step by appending 'WITHOUT VALIDATION' to the *note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table-partition-operations. statement.

The following example compares the difference between execution times when exchanging a partition with a nonpartitioned table, with and without validation. The partitioned table (table 'e') contains two partitions of 1 million rows each. The rows in p0 of table e are removed and p0 is exchanged with a nonpartitioned table of 1 million rows. The 'WITH VALIDATION' operation takes 0.74 seconds. By comparison, the 'WITHOUT VALIDATION' operation takes 0.01 seconds.

 # Create a partitioned table with 1 million rows in each partition

 CREATE TABLE e (
     id INT NOT NULL,
     fname VARCHAR(30),
     lname VARCHAR(30)
 )
     PARTITION BY RANGE (id) (
         PARTITION p0 VALUES LESS THAN (1000001),
         PARTITION p1 VALUES LESS THAN (2000001),
 );

 SELECT COUNT(*) FROM e;
 | COUNT(*) |
 +----------+
 |  2000000 |
 +----------+
 1 row in set (0.27 sec)

 # View the rows in each partition

 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
 +----------------+-------------+
 | PARTITION_NAME | TABLE_ROWS  |
 +----------------+-------------+
 | p0             |     1000000 |
 | p1             |     1000000 |
 +----------------+-------------+
 2 rows in set (0.00 sec)

 # Create a nonpartitioned table of the same structure and populate it with 1 million rows

 CREATE TABLE e2 (
     id INT NOT NULL,
     fname VARCHAR(30),
     lname VARCHAR(30)
 );

 mysql> SELECT COUNT(*) FROM e2;
 +----------+
 | COUNT(*) |
 +----------+
 |  1000000 |
 +----------+
 1 row in set (0.24 sec)

 # Create another nonpartitioned table of the same structure and populate it with 1 million rows

 CREATE TABLE e3 (
     id INT NOT NULL,
     fname VARCHAR(30),
     lname VARCHAR(30)
 );

 mysql> SELECT COUNT(*) FROM e3;
 +----------+
 | COUNT(*) |
 +----------+
 |  1000000 |
 +----------+
 1 row in set (0.25 sec)

 # Drop the rows from p0 of table e

 mysql> DELETE FROM e WHERE id < 1000001;
 Query OK, 1000000 rows affected (5.55 sec)

 # Confirm that there are no rows in partition p0

 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |          0 |
 | p1             |    1000000 |
 +----------------+------------+
 2 rows in set (0.00 sec)

 # Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
 Query OK, 0 rows affected (0.74 sec)

 # Confirm that the partition was exchanged with table e2

 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |    1000000 |
 | p1             |    1000000 |
 +----------------+------------+
 2 rows in set (0.00 sec)

 # Once again, drop the rows from p0 of table e

 mysql> DELETE FROM e WHERE id < 1000001;
 Query OK, 1000000 rows affected (5.55 sec)

 # Confirm that there are no rows in partition p0

 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |          0 |
 | p1             |    1000000 |
 +----------------+------------+
 2 rows in set (0.00 sec)

 # Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
 Query OK, 0 rows affected (0.01 sec)

 # Confirm that the partition was exchanged with table e3

 mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
 +----------------+------------+
 | PARTITION_NAME | TABLE_ROWS |
 +----------------+------------+
 | p0             |    1000000 |
 | p1             |    1000000 |
 +----------------+------------+
 2 rows in set (0.00 sec)

If a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using note 'REPAIR TABLE': repair-table. or note 'ALTER TABLE ... REPAIR PARTITION': alter-table-partition-operations.

Exchanging a Subpartition with a Nonpartitioned Table

You can also exchange a subpartition of a subpartitioned table (see note partitioning-subpartitions::) with a nonpartitioned table using an note 'ALTER TABLE ... EXCHANGE PARTITION': alter-table. statement. In the following example, we first create a table 'es' that is partitioned by 'RANGE' and subpartitioned by 'KEY', populate this table as we did table 'e', and then create an empty, nonpartitioned copy 'es2' of the table, as shown here:

 mysql> CREATE TABLE es (
     ->     id INT NOT NULL,
     ->     fname VARCHAR(30),
     ->     lname VARCHAR(30)
     -> )
     ->     PARTITION BY RANGE (id)
     ->     SUBPARTITION BY KEY (lname)
     ->     SUBPARTITIONS 2 (
     ->         PARTITION p0 VALUES LESS THAN (50),
     ->         PARTITION p1 VALUES LESS THAN (100),
     ->         PARTITION p2 VALUES LESS THAN (150),
     ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
     ->     );
 Query OK, 0 rows affected (2.76 sec)

 mysql> INSERT INTO es VALUES
     ->     (1669, "Jim", "Smith"),
     ->     (337, "Mary", "Jones"),
     ->     (16, "Frank", "White"),
     ->     (2005, "Linda", "Black");
 Query OK, 4 rows affected (0.04 sec)
 Records: 4  Duplicates: 0  Warnings: 0

 mysql> CREATE TABLE es2 LIKE es;
 Query OK, 0 rows affected (1.27 sec)

 mysql> ALTER TABLE es2 REMOVE PARTITIONING;
 Query OK, 0 rows affected (0.70 sec)
 Records: 0  Duplicates: 0  Warnings: 0

Although we did not explicitly name any of the subpartitions when creating table 'es', we can obtain generated names for these by including the 'SUBPARTITION_NAME' of the *note 'PARTITIONS': information-schema-partitions-table. table from 'INFORMATION_SCHEMA' when selecting from that table, as shown here:

 mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
     ->     FROM INFORMATION_SCHEMA.PARTITIONS
     ->     WHERE TABLE_NAME = 'es';
 +----------------+-------------------+------------+
 | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
 +----------------+-------------------+------------+
 | p0             | p0sp0             |          1 |
 | p0             | p0sp1             |          0 |
 | p1             | p1sp0             |          0 |
 | p1             | p1sp1             |          0 |
 | p2             | p2sp0             |          0 |
 | p2             | p2sp1             |          0 |
 | p3             | p3sp0             |          3 |
 | p3             | p3sp1             |          0 |
 +----------------+-------------------+------------+
 8 rows in set (0.00 sec)

The following *note 'ALTER TABLE': alter-table-partition-operations. statement exchanges subpartition 'p3sp0' table 'es' with the nonpartitioned table 'es2':

 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
 Query OK, 0 rows affected (0.29 sec)

You can verify that the rows were exchanged by issuing the following queries:

 mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
     ->     FROM INFORMATION_SCHEMA.PARTITIONS
     ->     WHERE TABLE_NAME = 'es';
 +----------------+-------------------+------------+
 | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
 +----------------+-------------------+------------+
 | p0             | p0sp0             |          1 |
 | p0             | p0sp1             |          0 |
 | p1             | p1sp0             |          0 |
 | p1             | p1sp1             |          0 |
 | p2             | p2sp0             |          0 |
 | p2             | p2sp1             |          0 |
 | p3             | p3sp0             |          0 |
 | p3             | p3sp1             |          0 |
 +----------------+-------------------+------------+
 8 rows in set (0.00 sec)

 mysql> SELECT * FROM es2;
 +------+-------+-------+
 | id   | fname | lname |
 +------+-------+-------+
 | 1669 | Jim   | Smith |
 |  337 | Mary  | Jones |
 | 2005 | Linda | Black |
 +------+-------+-------+
 3 rows in set (0.00 sec)

If a table is subpartitioned, you can exchange only a subpartition of the table--not an entire partition--with an unpartitioned table, as shown here:

 mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
 ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

The comparison of table structures used by MySQL is very strict. The number, order, names, and types of columns and indexes of the partitioned table and the nonpartitioned table must match exactly. In addition, both tables must use the same storage engine:

 mysql> CREATE TABLE es3 LIKE e;
 Query OK, 0 rows affected (1.31 sec)

 mysql> ALTER TABLE es3 REMOVE PARTITIONING;
 Query OK, 0 rows affected (0.53 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> SHOW CREATE TABLE es3\G
 *************************** 1. row ***************************
        Table: es3
 Create Table: CREATE TABLE `es3` (
   `id` int(11) NOT NULL,
   `fname` varchar(30) DEFAULT NULL,
   `lname` varchar(30) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)

 mysql> ALTER TABLE es3 ENGINE = MyISAM;
 Query OK, 0 rows affected (0.15 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
 ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

File: manual.info.tmp, Node: partitioning-maintenance, Next: partitioning-info, Prev: partitioning-management-exchange, Up: partitioning-management

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

Table maintenance of partitioned tables can be accomplished using the statements note 'CHECK TABLE': check-table, note 'OPTIMIZE TABLE': optimize-table, note 'ANALYZE TABLE': analyze-table, and note 'REPAIR TABLE': repair-table, which are supported for partitioned tables.

You can use a number of extensions to *note 'ALTER TABLE': alter-table-partition-operations. for performing operations of this type on one or more partitions directly, as described in the following list:

Rebuilding partitions

Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.

Example:
```
  ALTER TABLE t1 REBUILD PARTITION p0, p1;
```
Optimizing partitions

If you have deleted a large number of rows from a partition or if you have made many changes to a partitioned table with variable-length rows (that is, having note 'VARCHAR': char, note 'BLOB': blob, or note 'TEXT': blob. columns), you can use note 'ALTER TABLE ... OPTIMIZE PARTITION': alter-table-partition-operations. to reclaim any unused space and to defragment the partition data file.

Example:
```
  ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
```
Using 'OPTIMIZE PARTITION' on a given partition is equivalent to running 'CHECK PARTITION', 'ANALYZE PARTITION', and 'REPAIR PARTITION' on that partition.

Some MySQL storage engines, including note 'InnoDB': innodb-storage-engine, do not support per-partition optimization; in these cases, note 'ALTER TABLE ... OPTIMIZE PARTITION': alter-table-partition-operations. analyzes and rebuilds the entire table, and causes an appropriate warning to be issued. (Bug #11751825, Bug #42822) Use 'ALTER TABLE ... REBUILD PARTITION' and 'ALTER TABLE ... ANALYZE PARTITION' instead, to avoid this issue.
Analyzing partitions

This reads and stores the key distributions for partitions.

Example:
```
  ALTER TABLE t1 ANALYZE PARTITION p3;
```
Repairing partitions

This repairs corrupted partitions.

Example:
```
  ALTER TABLE t1 REPAIR PARTITION p0,p1;
```
Normally, 'REPAIR PARTITION' fails when the partition contains duplicate key errors. In MySQL 5.7.2 and later, you can use *note 'ALTER IGNORE TABLE': alter-table-partition-operations. with this option, in which case all rows that cannot be moved due to the presence of duplicate keys are removed from the partition (Bug #16900947).
Checking partitions

You can check partitions for errors in much the same way that you can use 'CHECK TABLE' with nonpartitioned tables.

Example:
```
  ALTER TABLE trb3 CHECK PARTITION p1;
```
This command tells you if the data or indexes in partition 'p1' of table 't1' are corrupted. If this is the case, use *note 'ALTER TABLE ... REPAIR PARTITION': alter-table-partition-operations. to repair the partition.

Normally, 'CHECK PARTITION' fails when the partition contains duplicate key errors. In MySQL 5.7.2 and later, you can use *note 'ALTER IGNORE TABLE': alter-table-partition-operations. with this option, in which case the statement returns the contents of each row in the partition where a duplicate key violation is found. Only the values for the columns in the partitioning expression for the table are reported. (Bug #16900947)

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.

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

In MySQL 5.7, you can also truncate partitions using note 'ALTER TABLE ... TRUNCATE PARTITION': alter-table. This statement can be used to delete all rows from one or more partitions in much the same way that note 'TRUNCATE TABLE': truncate-table. deletes all rows from a table.

*note 'ALTER TABLE ... TRUNCATE PARTITION ALL': alter-table. truncates all partitions in the table.

Prior to MySQL 5.7.2, 'ANALYZE', 'CHECK', 'OPTIMIZE', 'REBUILD', 'REPAIR', and 'TRUNCATE' operations were not permitted on subpartitions (Bug #14028340, Bug #65184).

File: manual.info.tmp, Node: partitioning-info, Prev: partitioning-maintenance, Up: partitioning-management

22.3.5 Obtaining Information About Partitions

This section discusses obtaining information about existing partitions, which can be done in a number of ways. Methods of obtaining such information include the following:

Using the *note 'SHOW CREATE TABLE': show-create-table. statement to view the partitioning clauses used in creating a partitioned table.
Using the *note 'SHOW TABLE STATUS': show-table-status. statement to determine whether a table is partitioned.
Querying the Information Schema *note 'PARTITIONS': information-schema-partitions-table. table.
Using the statement note 'EXPLAIN SELECT': explain. to see which partitions are used by a given note 'SELECT': select.

As discussed elsewhere in this chapter, *note 'SHOW CREATE TABLE': show-create-table. includes in its output the 'PARTITION BY' clause used to create a partitioned table. For example:

 mysql> SHOW CREATE TABLE trb3\G
 *************************** 1. row ***************************
        Table: trb3
 Create Table: CREATE TABLE `trb3` (
   `id` int(11) default NULL,
   `name` varchar(50) default NULL,
   `purchased` date default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY RANGE (YEAR(purchased)) (
   PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
   PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
   PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
   PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
 )
 1 row in set (0.00 sec)

The output from note 'SHOW TABLE STATUS': show-table-status. for partitioned tables is the same as that for nonpartitioned tables, except that the 'Create_options' column contains the string 'partitioned'. The 'Engine' column contains the name of the storage engine used by all partitions of the table. (See note show-table-status::, for more information about this statement.)

You can also obtain information about partitions from 'INFORMATION_SCHEMA', which contains a note 'PARTITIONS': information-schema-partitions-table. table. See note information-schema-partitions-table::.

It is possible to determine which partitions of a partitioned table are involved in a given note 'SELECT': select. query using note 'EXPLAIN': explain. The 'partitions' column in the *note 'EXPLAIN': explain. output lists the partitions from which records would be matched by the query.

Suppose that you have a table 'trb1' created and populated as follows:

 CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
     PARTITION BY RANGE(id)
     (
         PARTITION p0 VALUES LESS THAN (3),
         PARTITION p1 VALUES LESS THAN (7),
         PARTITION p2 VALUES LESS THAN (9),
         PARTITION p3 VALUES LESS THAN (11)
     );

 INSERT INTO trb1 VALUES
     (1, 'desk organiser', '2003-10-15'),
     (2, 'CD player', '1993-11-05'),
     (3, 'TV set', '1996-03-10'),
     (4, 'bookcase', '1982-01-10'),
     (5, 'exercise bike', '2004-05-09'),
     (6, 'sofa', '1987-06-05'),
     (7, 'popcorn maker', '2001-11-22'),
     (8, 'aquarium', '1992-08-04'),
     (9, 'study desk', '1984-09-16'),
     (10, 'lava lamp', '1998-12-25');

You can see which partitions are used in a query such as 'SELECT * FROM trb1;', as shown here:

 mysql> EXPLAIN SELECT * FROM trb1\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: trb1
    partitions: p0,p1,p2,p3
          type: ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 10
         Extra: Using filesort

In this case, all four partitions are searched. However, when a limiting condition making use of the partitioning key is added to the query, you can see that only those partitions containing matching values are scanned, as shown here:

 mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: trb1
    partitions: p0,p1
          type: ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 10
         Extra: Using where

*note 'EXPLAIN ': explain. also provides information about keys used and possible keys:

 mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
 Query OK, 10 rows affected (0.03 sec)
 Records: 10  Duplicates: 0  Warnings: 0

 mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: trb1
    partitions: p0,p1
          type: range
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 4
           ref: NULL
          rows: 7
         Extra: Using where

If *note 'EXPLAIN PARTITIONS': explain. is used to examine a query against a nonpartitioned table, no error is produced, but the value of the 'partitions' column is always 'NULL'.

The 'rows' column of *note 'EXPLAIN': explain. output displays the total number of rows in the table.