Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.8Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 311.6Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  Partitioning

Chapter 19 Partitioning

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, like this:

Note

Previous versions of MySQL had the have_partitioning variable, which was deprecated and removed in MySQL 5.6.1.

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.6 Community binaries provided by Oracle include partitioning support. For information about partitioning support offered in MySQL Enterprise Edition binaries, see Chapter 25, MySQL Enterprise Edition.

To enable partitioning if you are compiling MySQL 5.6 from source, the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option. For more information, 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.

See Section 19.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 19.2, “Partitioning Types”, and Section 19.2.6, “Subpartitioning”.

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

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

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

For known issues with partitioning in MySQL 5.6, see Section 19.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 Partitioning Forum

    This is the official discussion forum for those interested in or experimenting with MySQL Partitioning technology. It features announcements and updates from MySQL developers and others. It is monitored by members of the Partitioning Development and Documentation Teams.

  • Mikael Ronström's Blog

    MySQL Partitioning Architect and Lead Developer Mikael Ronström frequently posts articles here concerning his work with MySQL Partitioning and MySQL Cluster.

  • PlanetMySQL

    A MySQL news site featuring MySQL-related blogs, which should be of interest to anyone using my MySQL. We encourage you to check here for links to blogs kept by those working with MySQL Partitioning, or to have your own blog added to those covered.

MySQL 5.6 binaries are available from http://dev.mysql.com/downloads/mysql/5.6.html. However, for the latest partitioning bugfixes and feature additions, you can obtain the source from our GitHub 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.6 build, check the MySQL Partitioning Forum and ask for assistance there if you do not find a solution to your problem already posted.


User Comments
Sign Up Login You must be logged in to post a comment.