Chapter 17. Partitioning

Table of Contents

17.1. Overview of Partitioning in MySQL
17.2. Partitioning Types
17.2.1. RANGE Partitioning
17.2.2. LIST Partitioning
17.2.3. COLUMNS Partitioning
17.2.4. HASH Partitioning
17.2.5. KEY Partitioning
17.2.6. Subpartitioning
17.2.7. How MySQL Partitioning Handles NULL
17.3. Partition Management
17.3.1. Management of RANGE and LIST Partitions
17.3.2. Management of HASH and KEYPartitions
17.3.3. Exchanging Partitions and Subpartitions with Tables
17.3.4. Maintenance of Partitions
17.3.5. Obtaining Information About Partitions
17.4. Partition Pruning
17.5. Partition Selection
17.6. Restrictions and Limitations on Partitioning
17.6.1. Partitioning Keys, Primary Keys, and Unique Keys
17.6.2. Partitioning Limitations Relating to Storage Engines
17.6.3. Partitioning Limitations Relating to Functions
17.6.4. Partitioning and Locking

This chapter discusses MySQL's implementation of user-defined partitioning. You can determine whether your MySQL Server supports partitioning by checking the output of the SHOW PLUGINS statement, as shown here:

mysql> SHOW PLUGINS;+------------+----------+----------------+---------+---------+| Name       | Status   | Type           | Library | License |+------------+----------+----------------+---------+---------+| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     || MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     || ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |+------------+----------+----------------+---------+---------+11 rows in set (0.00 sec)

You can also check the INFORMATION_SCHEMA.PLUGINS table with a query similar to this one:

mysql> SELECT     ->     PLUGIN_NAME as Name,     ->     PLUGIN_VERSION
        as Version,     ->     PLUGIN_STATUS as
        Status     -> FROM INFORMATION_SCHEMA.PLUGINS     -> WHERE PLUGIN_TYPE='STORAGE ENGINE';+--------------------+---------+--------+| Name               | Version | Status |+--------------------+---------+--------+| binlog             | 1.0     | ACTIVE || CSV                | 1.0     | ACTIVE || MEMORY             | 1.0     | ACTIVE || MRG_MYISAM         | 1.0     | ACTIVE || MyISAM             | 1.0     | ACTIVE || PERFORMANCE_SCHEMA | 0.1     | ACTIVE || BLACKHOLE          | 1.0     | ACTIVE || ARCHIVE            | 3.0     | ACTIVE || InnoDB             | 5.6     | ACTIVE || partition | 1.0 | ACTIVE |+--------------------+---------+--------+10 rows in set (0.00 sec)

In either case, if you do not see the partition plugin listed with the value ACTIVE for the Status column in the output (shown in bold text in each of the examples just given), then your version of MySQL was not built with partitioning support.

MySQL 5.7 Community binaries provided by Oracle include partitioning support. For information about partitioning support offered in commercial MySQL Server binaries, see MySQL Enterprise Server 5.1 on the MySQL Web site at

To enable partitioning if you are compiling MySQL 5.7 from source, the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option. For more information about building MySQL, see Section 2.9, "Installing MySQL from Source".

If your MySQL binary is built with partitioning support, nothing further needs to be done to enable it (for example, no special entries are required in your my.cnf file).

If you want to disable partitioning support, you can start the MySQL Server with the --skip-partition option, in which case the value of have_partitioning is DISABLED. When partitioning support is disabled, you can see any existing partitioned tables and drop them (although doing this is not advised), but you cannot otherwise manipulate them or access their data.

An introduction to partitioning and partitioning concepts may be found in Section 17.1, "Overview of Partitioning in MySQL".

MySQL supports several types of partitioning, which are discussed in Section 17.2, "Partitioning Types", as well as subpartitioning, which is described in Section 17.2.6, "Subpartitioning".

Methods of adding, removing, and altering partitions in existing partitioned tables are covered in Section 17.3, "Partition Management".

Table maintenance commands for use with partitioned tables are discussed in Section 17.3.4, "Maintenance of Partitions".

The PARTITIONS table in the INFORMATION_SCHEMA database provides information about partitions and partitioned tables. See Section 19.14, "The INFORMATION_SCHEMA PARTITIONS Table", for more information; for some examples of queries against this table, see Section 17.2.7, "How MySQL Partitioning Handles NULL".

For known issues with partitioning in MySQL 5.7, see Section 17.6, "Restrictions and Limitations on Partitioning".

You may also find the following resources to be useful when working with partitioned tables.

Additional Resources. Other sources of information about user-defined partitioning in MySQL include the following:

MySQL 5.7 binaries are available from However, for the latest partitioning bugfixes and feature additions, you can obtain the source from our Bazaar repository. To enable partitioning, the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option. For more information about building MySQL, see Section 2.9, "Installing MySQL from Source". If you have problems compiling a partitioning-enabled MySQL 5.7 build, check the MySQL Partitioning Forum and ask for assistance there if you do not find a solution to your problem already posted. - all specs in one place