The world's most popular open source database
Table of Contents [+/-]
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
SHOW VARIABLES LIKE '%partition%';+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)
Prior to MySQL 5.1.6, this variable was named
You can also check the output of the
PLUGINS statement, as shown here:
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
YES listed in the output of an appropriate
SHOW VARIABLES statement, or if you
do not see the
partition plugin listed with the
ACTIVE for the
column in the output of
(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 Sun Microsystems include partitioning support. For information about partitioning support offered in commercial MySQL Server binaries, see MySQL Enterprise Server 5.1, on the MySQL website.
If you are compiling MySQL 5.1 from source, the build
must be configured using
--with-partition to enable
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
If you want to disable partitioning support, you can start the MySQL
Server with the
option, in which case the value of
DISABLED. However, if you do this, you cannot
access any partitioned tables until the server is once again
restarted without the
An introduction to partitioning and partitioning concepts may be found in Section 18.1, “Overview of Partitioning in MySQL”.
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
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
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 Section C.1.38, “Changes in MySQL 5.1.6 (01 February 2006)” for more information and suggested workarounds before upgrading to MySQL 5.1.6 or later.
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:
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.
MySQL Partitioning Architect and Lead Developer Mikael Ronström frequently posts articles here concerning his work with MySQL Partitioning and MySQL Cluster.
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.1 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, 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.