Chapter 18 Partitioning

Table of Contents

18.1 Overview of Partitioning in MySQL
18.2 Partitioning Types
18.2.1 RANGE Partitioning
18.2.2 LIST Partitioning
18.2.3 HASH Partitioning
18.2.4 KEY Partitioning
18.2.5 Subpartitioning
18.2.6 How MySQL Partitioning Handles NULL
18.3 Partition Management
18.3.1 Management of RANGE and LIST Partitions
18.3.2 Management of HASH and KEY Partitions
18.3.3 Maintenance of Partitions
18.3.4 Obtaining Information About Partitions
18.4 Partition Pruning
18.5 Restrictions and Limitations on Partitioning
18.5.1 Partitioning Keys, Primary Keys, and Unique Keys
18.5.2 Partitioning Limitations Relating to Storage Engines
18.5.3 Partitioning Limitations Relating to Functions
18.5.4 Partitioning and Table-Level Locking

This chapter discusses MySQL's implementation of user-defined partitioning. You can determine whether your MySQL Server supports partitioning by means of a SHOW VARIABLES statement such as this one:

Note

The have_partitioning variable is deprecated, and removed in MySQL 5.6.1.

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)
Note

Prior to MySQL 5.1.6, this variable was named have_partition_engine. (Bug #16718)

You can also check the output of the SHOW PLUGINS statement, like this:

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)

In MySQL 5.1.5 and later, 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)

Whichever of the preceding statements you employ, 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.1 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 http://www.mysql.com/products/enterprise/server.html.

If you are compiling MySQL 5.1 from source, the build must be configured using --with-partition to enable partitioning. Using --with-plugins=max to configure the build includes this option automatically. For more information, see Section 2.11, “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.

See Section 18.1, “Overview of Partitioning in MySQL”, for an introduction to partitioning and partitioning concepts.

MySQL supports several types of partitioning as well as subpartitioning; see Section 18.2, “Partitioning Types”, and Section 18.2.5, “Subpartitioning”.

Section 18.3, “Partition Management”, covers methods of adding, removing, and altering partitions in existing partitioned tables.

Section 18.3.3, “Maintenance of Partitions”, discusses table maintenance commands for use with partitioned tables.

Beginning with MySQL 5.1.6, the PARTITIONS table in the INFORMATION_SCHEMA database provides information about partitions and partitioned tables. See Section 20.11, “The INFORMATION_SCHEMA PARTITIONS Table”, for more information; for some examples of queries against this table, see Section 18.2.6, “How MySQL Partitioning Handles NULL”.

Important

Partitioned tables created with MySQL versions prior to 5.1.6 cannot be read by a 5.1.6 or later MySQL Server. In addition, the INFORMATION_SCHEMA.TABLES table cannot be used if such tables are present on a 5.1.6 server. Beginning with MySQL 5.1.7, a suitable warning message is generated instead, to alert the user that incompatible partitioned tables have been found by the server.

If you are using partitioned tables which were created in MySQL 5.1.5 or earlier, be sure to see the Release Notes for MySQL 5.1.6 for more information and suggested workarounds before upgrading to MySQL 5.1.6 or later.

For known issues with partitioning in MySQL 5.1, see Section 18.5, “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.1 binaries are available from http://dev.mysql.com/downloads/mysql/5.1.html. However, for the latest partitioning bugfixes and feature additions, you can obtain the source from our Bazaar repository. To enable partitioning, you need to compile the server using the --with-partition option. For more information about building MySQL, see Section 2.11, “Installing MySQL from Source”. If you have problems compiling a partitioning-enabled MySQL 5.1 build, check the MySQL Partitioning Forum and ask for assistance there if you do not find a solution to your problem already posted.