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 command such as this one:

mysql> SHOW VARIABLES LIKE '%partition%';

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

You can also check 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)

If you do not see the have_partitioning variable with the value YES listed in the output of an appropriate SHOW VARIABLES statement, or if you do not see the partition plugin listed with the value ACTIVE for the Status column in the output of SHOW PLUGINS (show in bold text in the example just given), then your version of MySQL was not built with partitioning support.

MySQL Community binaries provided by Oracle Corporation 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.

If you are compiling MySQL 5.1 from source, the build must be configured using --with-partition to enable partitioning.

If your MySQL binary is built with partitioning support, nothing further needs to be done in order 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. However, if you do this, you cannot access any partitioned tables until the server is once again restarted without the --skip-partition option.

An introduction to partitioning and partitioning concepts may be found in Section 18.1, “Overview of Partitioning in MySQL”.

MySQL supports several types of partitioning, which are discussed in Section 18.2, “Partition Types”, as well as subpartitioning, which is described in Section 18.2.5, “Subpartitioning”.

Methods of adding, removing, and altering partitions in existing partitioned tables are covered in Section 18.3, “Partition Management”.

Table maintenance commands for use with partitioned tables are discussed in Section 18.3.3, “Maintenance of Partitions”.

Beginning with MySQL 5.1.6, the PARTITIONS table in the INFORMATION_SCHEMA database provides information about partitions and partitioned tables. See Section 20.19, “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”.

The partitioning implementation in MySQL 5.1 is still undergoing development. For known issues with MySQL partitioning, see Section 18.5, “Restrictions and Limitations on Partitioning”, where we have noted these.

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.3, “MySQL Installation Using a Source Distribution”. 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.