<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 18. Partitioning</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="mysql-cluster.html" title="Chapter 17. MySQL Cluster NDB 6.X/7.X"><link rel="next" href="stored-programs-views.html" title="Chapter 19. Stored Programs and Views"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 18. Partitioning</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="mysql-cluster.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="stored-programs-views.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="partitioning"></a>Chapter 18. Partitioning</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="partitioning.html#partitioning-overview">18.1. Overview of Partitioning in MySQL</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-types">18.2. Partition Types</a></span></dt><dd><dl><dt><span class="section"><a href="partitioning.html#partitioning-range">18.2.1. <code class="literal">RANGE</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-list">18.2.2. <code class="literal">LIST</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-hash">18.2.3. <code class="literal">HASH</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-key">18.2.4. <code class="literal">KEY</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-subpartitions">18.2.5. Subpartitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-handling-nulls">18.2.6. How MySQL Partitioning Handles <code class="literal">NULL</code></a></span></dt></dl></dd><dt><span class="section"><a href="partitioning.html#partitioning-management">18.3. Partition Management</a></span></dt><dd><dl><dt><span class="section"><a href="partitioning.html#partitioning-management-range-list">18.3.1. Management of <code class="literal">RANGE</code> and <code class="literal">LIST</code>
Partitions</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-management-hash-key">18.3.2. Management of <code class="literal">HASH</code> and <code class="literal">KEY</code>
Partitions</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-maintenance">18.3.3. Maintenance of Partitions</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-info">18.3.4. Obtaining Information About Partitions</a></span></dt></dl></dd><dt><span class="section"><a href="partitioning.html#partitioning-pruning">18.4. Partition Pruning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-limitations">18.5. Restrictions and Limitations on Partitioning</a></span></dt><dd><dl><dt><span class="section"><a href="partitioning.html#partitioning-limitations-partitioning-keys-unique-keys">18.5.1. Partitioning Keys, Primary Keys, and Unique Keys</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-limitations-storage-engines">18.5.2. Partitioning Limitations Relating to Storage Engines</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-limitations-functions">18.5.3. Partitioning Limitations Relating to Functions</a></span></dt></dl></dd></dl></div><a class="indexterm" name="id4818815"></a><a class="indexterm" name="id4818824"></a><a class="indexterm" name="id4818832"></a><a class="indexterm" name="id4818845"></a><p>
This chapter discusses MySQL's implementation of
<em class="firstterm">user-defined partitioning</em>. You can determine
whether your MySQL Server supports partitioning by means of a
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a> command such as this
one:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW VARIABLES LIKE '%partition%';</code></strong>
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Prior to MySQL 5.1.6, this variable was named
<code class="literal">have_partition_engine</code>. (<a href="http://bugs.mysql.com/16718" target="_top">Bug#16718</a>)
</p></div><p>
You can also check the output of the <a href="sql-syntax.html#show-plugins" title="12.5.5.27. SHOW PLUGINS Syntax"><code class="literal">SHOW
PLUGINS</code></a> statement, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW PLUGINS;</code></strong>
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
<span class="bold"><strong>| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |</strong></span>
| 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)
</pre><p>
If you do not see the
<a href="server-administration.html#sysvar_have_partitioning"><code class="literal">have_partitioning</code></a> variable with the
value <code class="literal">YES</code> listed in the output of an appropriate
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a> statement, or if you
do not see the <code class="literal">partition</code> plugin listed with the
value <code class="literal">ACTIVE</code> for the <code class="literal">Status</code>
column in the output of <a href="sql-syntax.html#show-plugins" title="12.5.5.27. SHOW PLUGINS Syntax"><code class="literal">SHOW PLUGINS</code></a>
(show in bold text in the example just given), then your version of
MySQL was not built with partitioning support.
</p><p>
MySQL Community binaries provided by Sun Microsystems include
partitioning support. For information about partitioning support
offered in commercial MySQL Server binaries, see
<a href="http://www.mysql.com/products/enterprise/server.html" target="_top"><em class="citetitle">MySQL
Enterprise Server 5.1</em></a>, on the MySQL website.
</p><p>
If you are compiling MySQL 5.1 from source, the build
must be configured using <code class="option">--with-partition</code> to enable
partitioning.
</p><p>
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 <code class="filename">my.cnf</code>
file).
</p><p>
If you want to disable partitioning support, you can start the MySQL
Server with the <a href="server-administration.html#option_mysqld_skip-partition"><code class="option">--skip-partition</code></a>
option, in which case the value of
<a href="server-administration.html#sysvar_have_partitioning"><code class="literal">have_partitioning</code></a> is
<code class="literal">DISABLED</code>. However, if you do this, you cannot
access any partitioned tables until the server is once again
restarted without the
<a href="server-administration.html#option_mysqld_skip-partition"><code class="option">--skip-partition</code></a> option.
</p><p>
An introduction to partitioning and partitioning concepts may be
found in <a href="partitioning.html#partitioning-overview" title="18.1. Overview of Partitioning in MySQL">Section 18.1, “Overview of Partitioning in MySQL”</a>.
</p><p>
MySQL supports several types of partitioning, which are discussed in
<a href="partitioning.html#partitioning-types" title="18.2. Partition Types">Section 18.2, “Partition Types”</a>, as well as subpartitioning,
which is described in <a href="partitioning.html#partitioning-subpartitions" title="18.2.5. Subpartitioning">Section 18.2.5, “Subpartitioning”</a>.
</p><p>
Methods of adding, removing, and altering partitions in existing
partitioned tables are covered in
<a href="partitioning.html#partitioning-management" title="18.3. Partition Management">Section 18.3, “Partition Management”</a>.
</p><p>
Table maintenance commands for use with partitioned tables are
discussed in <a href="partitioning.html#partitioning-maintenance" title="18.3.3. Maintenance of Partitions">Section 18.3.3, “Maintenance of Partitions”</a>.
</p><p>
Beginning with MySQL 5.1.6, the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table in the
<code class="literal">INFORMATION_SCHEMA</code> database provides information
about partitions and partitioned tables. See
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table">Section 20.19, “The <code class="literal">INFORMATION_SCHEMA PARTITIONS</code> Table”</a>, for more information; for some
examples of queries against this table, see
<a href="partitioning.html#partitioning-handling-nulls" title="18.2.6. How MySQL Partitioning Handles NULL">Section 18.2.6, “How MySQL Partitioning Handles <code class="literal">NULL</code>”</a>.
</p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
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
<a href="information-schema.html#tables-table" title="20.2. The INFORMATION_SCHEMA TABLES Table"><code class="literal">INFORMATION_SCHEMA.TABLES</code></a> 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.
</p><p>
If you are using partitioned tables which were created in MySQL
5.1.5 or earlier, be sure to see <a href="news.html#news-5-1-6" title="C.1.39. Changes in MySQL 5.1.6 (01 February 2006)">Section C.1.39, “Changes in MySQL 5.1.6 (01 February 2006)”</a> for
more information and suggested workarounds
<span class="emphasis"><em>before</em></span> upgrading to MySQL 5.1.6 or later.
</p></div><p>
The partitioning implementation in MySQL 5.1 is still
undergoing development. For known issues with MySQL partitioning,
see <a href="partitioning.html#partitioning-limitations" title="18.5. Restrictions and Limitations on Partitioning">Section 18.5, “Restrictions and Limitations on Partitioning”</a>, where we have noted
these.
</p><p>
You may also find the following resources to be useful when working
with partitioned tables.
</p><a class="indexterm" name="id4819168"></a><p><b>Additional Resources. </b>
Other sources of information about user-defined partitioning in
MySQL include the following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="http://forums.mysql.com/list.php?106" target="_top">MySQL Partitioning
Forum</a>
</p><p>
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.
</p></li><li><p>
<a href="http://mikaelronstrom.blogspot.com/" target="_top">Mikael
Ronström's Blog</a>
</p><p>
MySQL Partitioning Architect and Lead Developer Mikael
Ronström frequently posts articles here concerning his work
with MySQL Partitioning and MySQL Cluster.
</p></li><li><p>
<a href="http://www.planetmysql.org/" target="_top">PlanetMySQL</a>
</p><p>
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.
</p></li></ul></div><p>
</p><p>
MySQL 5.1 binaries are available from
<a href="http://dev.mysql.com/downloads/mysql/5.1.html" target="_top">http://dev.mysql.com/downloads/mysql/5.1.html</a>.
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
<code class="option">--with-partition</code> option. For more information about
building MySQL, see <a href="installing.html#installing-source" title="2.3. MySQL Installation Using a Source Distribution">Section 2.3, “MySQL Installation Using a Source Distribution”</a>. If you have
problems compiling a partitioning-enabled MySQL 5.1
build, check the <a href="http://forums.mysql.com/list.php?106" target="_top">MySQL
Partitioning Forum</a> and ask for assistance there if you do
not find a solution to your problem already posted.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="partitioning-overview"></a>18.1. Overview of Partitioning in MySQL</h2></div></div></div><a class="indexterm" name="id4819285"></a><p>
This section provides a conceptual overview of partitioning in
MySQL 5.1.
</p><p>
For information on partitioning restrictions and feature
limitations, see <a href="partitioning.html#partitioning-limitations" title="18.5. Restrictions and Limitations on Partitioning">Section 18.5, “Restrictions and Limitations on Partitioning”</a>.
</p><p>
The SQL standard does not provide much in the way of guidance
regarding the physical aspects of data storage. The SQL language
itself is intended to work independently of any data structures or
media underlying the schemas, tables, rows, or columns with which
it works. Nonetheless, most advanced database management systems
have evolved some means of determining the physical location to be
used for storing specific pieces of data in terms of the file
system, hardware or even both. In MySQL, the
<code class="literal">InnoDB</code> storage engine has long supported the
notion of a tablespace, and the MySQL Server, even prior to the
introduction of partitioning, could be configured to employ
different physical directories for storing different databases
(see <a href="optimization.html#symbolic-links" title="7.6.1. Using Symbolic Links">Section 7.6.1, “Using Symbolic Links”</a>, for an explanation of how
this is done).
</p><p>
<em class="firstterm">Partitioning</em> takes this notion a step
further, by allowing you to distribute portions of individual
tables across a file system according to rules which you can set
largely as needed. In effect, different portions of a table are
stored as separate tables in different locations. The
user-selected rule by which the division of data is accomplished
is known as a <em class="firstterm">partitioning function</em>, which
in MySQL can be the modulus, simple matching against a set of
ranges or value lists, an internal hashing function, or a linear
hashing function. The function is selected according to the
partitioning type specified by the user, and takes as its
parameter the value of a user-supplied expression. This expression
can be either an integer column value, or a function acting on one
or more column values and returning an integer. The value of this
expression is passed to the partitioning function, which returns
an integer value representing the number of the partition in which
that particular record should be stored. This function must be
nonconstant and nonrandom. It may not contain any queries, but may
use an SQL expression that is valid in MySQL, as long as that
expression returns either <code class="literal">NULL</code> or an integer
<em class="replaceable"><code>intval</code></em> such that
</p><pre class="programlisting">-MAXVALUE <= <em class="replaceable"><code>intval</code></em> <= MAXVALUE
</pre><p>
(<code class="literal">MAXVALUE</code> is used to represent the least upper
bound for the type of integer in question.
<code class="literal">-MAXVALUE</code> represents the greatest lower bound.)
There are some additional restrictions on partitioning functions;
see <a href="partitioning.html#partitioning-limitations" title="18.5. Restrictions and Limitations on Partitioning">Section 18.5, “Restrictions and Limitations on Partitioning”</a>, for more
information about these.
</p><p>
Examples of partitioning functions can be found in the discussions
of partitioning types later in this chapter (see
<a href="partitioning.html#partitioning-types" title="18.2. Partition Types">Section 18.2, “Partition Types”</a>), as well as in the
partitioning syntax descriptions given in
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax">Section 12.1.17, “<code class="literal">CREATE TABLE</code> Syntax”</a>.
</p><p>
This is known as <em class="firstterm">horizontal partitioning</em>
— that is, different rows of a table may be assigned to
different physical partitions. MySQL 5.1 does not
support <em class="firstterm">vertical partitioning</em>, in which
different columns of a table are assigned to different physical
partitions. There are not at this time any plans to introduce
vertical partitioning into MySQL 5.1.
</p><p>
For information about determining whether your MySQL Server binary
supports user-defined partitioning, see
<a href="partitioning.html" title="Chapter 18. Partitioning">Chapter 18, <i>Partitioning</i></a>.
</p><p>
For creating partitioned tables, you can use most storage engines
that are supported by your MySQL server; the MySQL partitioning
engine runs in a separate layer and can interact with any of
these. In MySQL 5.1, all partitions of the same
partitioned table must use the same storage engine; for
example, you cannot use <code class="literal">MyISAM</code> for one
partition and <code class="literal">InnoDB</code> for another. However,
there is nothing preventing you from using different storage
engines for different partitioned tables on the same MySQL server
or even in the same database.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
MySQL partitioning cannot be used with the
<code class="literal">MERGE</code> or <code class="literal">CSV</code> storage
engines. Beginning with MySQL 5.1.15,
<code class="literal">FEDERATED</code> tables also cannot be partitioned
(<a href="http://bugs.mysql.com/22451" target="_top">Bug#22451</a>). Prior to MySQL 5.1.6, it was also not feasible to
create a partitioned table using the
<code class="literal">BLACKHOLE</code> storage engine (<a href="http://bugs.mysql.com/14524" target="_top">Bug#14524</a>).
</p><p>
Partitioning by <code class="literal">KEY</code> or <code class="literal">LINEAR
KEY</code> is possible with
<code class="literal">NDBCLUSTER</code>, but other types of
user-defined partitioning are not supported for tables using
this storage engine. In addition, an
<code class="literal">NDBCLUSTER</code> table that employs
user-defined partitioning must have an explicit primary key, and
any columns referenced in the table's partitioning
expression must be part of the primary key. However, if no
columns are listed in the <code class="literal">PARTITION BY KEY</code> or
<code class="literal">PARTITION BY LINEAR KEY</code> clause of the
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> or
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statement used to
create or modify a user-partitioned
<code class="literal">NDBCLUSTER</code> table, then the table is
not required to have an explicit primary key.
</p><p>
For more information, see
<a href="partitioning.html#partitioning-limitations-storage-engines" title="18.5.2. Partitioning Limitations Relating to Storage Engines">Section 18.5.2, “Partitioning Limitations Relating to Storage Engines”</a>, and
<a href="mysql-cluster.html#mysql-cluster-limitations-syntax" title="17.1.5.1. Noncompliance with SQL Syntax in MySQL Cluster">Section 17.1.5.1, “Noncompliance with SQL Syntax in MySQL Cluster”</a>.
</p></div><p>
To employ a particular storage engine for a partitioned table, it
is necessary only to use the <code class="literal">[STORAGE] ENGINE</code>
option just as you would for a nonpartitioned table. However, you
should keep in mind that <code class="literal">[STORAGE] ENGINE</code> (and
other table options) need to be listed <span class="emphasis"><em>before</em></span>
any partitioning options are used in a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement. This example shows how to create a
table that is partitioned by hash into 6 partitions and which uses
the <code class="literal">InnoDB</code> storage engine:
</p><pre class="programlisting">CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Each <code class="literal">PARTITION</code> clause can include a
<code class="literal">[STORAGE] ENGINE</code> option, but in MySQL
5.1 this has no effect.
</p></div><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
Partitioning applies to all data and indexes of a table; you
cannot partition only the data and not the indexes, or
<span class="foreignphrase"><em class="foreignphrase">vice versa</em></span>, nor can you partition
only a portion of the table.
</p></div><p>
Data and indexes for each partition can be assigned to a specific
directory using the <code class="literal">DATA DIRECTORY</code> and
<code class="literal">INDEX DIRECTORY</code> options for the
<code class="literal">PARTITION</code> clause of the
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement used to
create the partitioned table.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Prior to MySQL 5.1.18, these options were permitted even when
the <a href="server-administration.html#sqlmode_no_dir_in_create"><code class="literal">NO_DIR_IN_CREATE</code></a> server
SQL mode was in effect. (<a href="http://bugs.mysql.com/24633" target="_top">Bug#24633</a>)
</p><p>
The <code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
DIRECTORY</code> options have no effect when defining
partitions for tables using the <code class="literal">InnoDB</code>
storage engine.
</p><p>
<code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
DIRECTORY</code> are not supported for individual
partitions or subpartitions on Windows. Beginning with MySQL
5.1.24, these options are ignored on Windows, except that a
warning is generated. (<a href="http://bugs.mysql.com/30459" target="_top">Bug#30459</a>)
</p></div><p>
</p><p>
In addition, <code class="literal">MAX_ROWS</code> and
<code class="literal">MIN_ROWS</code> can be used to determine the maximum
and minimum numbers of rows, respectively, that can be stored in
each partition. See <a href="partitioning.html#partitioning-management" title="18.3. Partition Management">Section 18.3, “Partition Management”</a>, for
more information on these options.
</p><a class="indexterm" name="id4819770"></a><p>
Some of the advantages of partitioning include:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Being able to store more data in one table than can be held on
a single disk or file system partition.
</p></li><li><p>
Data that loses its usefulness can often be easily be removed
from the table by dropping the partition containing only that
data. Conversely, the process of adding new data can in some
cases be greatly facilitated by adding a new partition
specifically for that data.
</p></li><li><p>
Some queries can be greatly optimized in virtue of the fact
that data satisfying a given <code class="literal">WHERE</code> clause
can be stored only on one or more partitions, thereby
excluding any remaining partitions from the search. Because
partitions can be altered after a partitioned table has been
created, you can reorganize your data to enhance frequent
queries that may not have been so when the partitioning scheme
was first set up. This capability, sometimes referred to as
<em class="firstterm">partition pruning</em>, was implemented in
MySQL 5.1.6. For more information, see
<a href="partitioning.html#partitioning-pruning" title="18.4. Partition Pruning">Section 18.4, “Partition Pruning”</a>.
</p></li></ul></div><p>
Other benefits usually associated with partitioning include those
in the following list. These features are not currently
implemented in MySQL Partitioning, but are high on our list of
priorities.
</p><div class="itemizedlist"><ul type="disc"><li><p>
Queries involving aggregate functions such as
<a href="functions.html#function_sum"><code class="literal">SUM()</code></a> and
<a href="functions.html#function_count"><code class="literal">COUNT()</code></a> can easily be
parallelized. A simple example of such a query might be
<code class="literal">SELECT salesperson_id, COUNT(orders) as order_total
FROM sales GROUP BY salesperson_id;</code>. By
“<span class="quote">parallelized,</span>” we mean that the query can be run
simultaneously on each partition, and the final result
obtained merely by summing the results obtained for all
partitions.
</p></li><li><p>
Achieving greater query throughput in virtue of spreading data
seeks over multiple disks.
</p></li></ul></div><p>
Be sure to check this section and chapter frequently for updates
as Partitioning development continues.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="partitioning-types"></a>18.2. Partition Types</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="partitioning.html#partitioning-range">18.2.1. <code class="literal">RANGE</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-list">18.2.2. <code class="literal">LIST</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-hash">18.2.3. <code class="literal">HASH</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-key">18.2.4. <code class="literal">KEY</code> Partitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-subpartitions">18.2.5. Subpartitioning</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-handling-nulls">18.2.6. How MySQL Partitioning Handles <code class="literal">NULL</code></a></span></dt></dl></div><a class="indexterm" name="id4819895"></a><p>
This section discusses the types of partitioning which are
available in MySQL 5.1. These include:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="bold"><strong><code class="literal">RANGE</code>
partitioning</strong></span>: Assigns rows to partitions based on
column values falling within a given range. See
<a href="partitioning.html#partitioning-range" title="18.2.1. RANGE Partitioning">Section 18.2.1, “<code class="literal">RANGE</code> Partitioning”</a>.
</p></li><li><p>
<span class="bold"><strong><code class="literal">LIST</code>
partitioning</strong></span>: Similar to partitioning by range,
except that the partition is selected based on columns
matching one of a set of discrete values. See
<a href="partitioning.html#partitioning-list" title="18.2.2. LIST Partitioning">Section 18.2.2, “<code class="literal">LIST</code> Partitioning”</a>.
</p></li><li><p>
<span class="bold"><strong><code class="literal">HASH</code>
partitioning</strong></span>: A partition is selected based on the
value returned by a user-defined expression that operates on
column values in rows to be inserted into the table. The
function may consist of any expression valid in MySQL that
yields a nonnegative integer value. See
<a href="partitioning.html#partitioning-hash" title="18.2.3. HASH Partitioning">Section 18.2.3, “<code class="literal">HASH</code> Partitioning”</a>.
</p></li><li><p>
<span class="bold"><strong><code class="literal">KEY</code>
partitioning</strong></span>: Similar to partitioning by hash,
except that only one or more columns to be evaluated are
supplied, and the MySQL server provides its own hashing
function. These columns can contain other than integer values,
since the hashing function supplied by MySQL guarantees an
integer result regardless of the column data type. See
<a href="partitioning.html#partitioning-key" title="18.2.4. KEY Partitioning">Section 18.2.4, “<code class="literal">KEY</code> Partitioning”</a>.
</p></li></ul></div><a class="indexterm" name="id4820009"></a><a class="indexterm" name="id4820021"></a><p>
A very common use of database partitioning is to segregate data by
date. Some database systems support explicit date partitioning,
which MySQL does not implement in 5.1. However, it is
not difficult in MySQL to create partitioning schemes based on
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>,
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>, or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> columns, or based on
expressions making use of such columns.
</p><p>
When partitioning by <code class="literal">KEY</code> or <code class="literal">LINEAR
KEY</code>, you can use a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>,
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>, or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> column as the partitioning
column without performing any modification of the column value.
For example, this table creation statement is perfectly valid in
MySQL:
</p><pre class="programlisting">CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;
</pre><p>
MySQL's other partitioning types, however, require a partitioning
expression that yields an integer value or
<code class="literal">NULL</code>. If you wish to use date-based
partitioning by <code class="literal">RANGE</code>, <code class="literal">LIST</code>,
<code class="literal">HASH</code>, or <code class="literal">LINEAR HASH</code>, you
can simply employ a function that operates on a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>,
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>, or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> column and returns such a
value, as shown here:
</p><pre class="programlisting">CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
</pre><p>
Additional examples of partitioning using dates may be found here:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="partitioning.html#partitioning-range" title="18.2.1. RANGE Partitioning">Section 18.2.1, “<code class="literal">RANGE</code> Partitioning”</a>
</p></li><li><p>
<a href="partitioning.html#partitioning-hash" title="18.2.3. HASH Partitioning">Section 18.2.3, “<code class="literal">HASH</code> Partitioning”</a>
</p></li><li><p>
<a href="partitioning.html#partitioning-linear-hash" title="18.2.3.1. LINEAR HASH Partitioning">Section 18.2.3.1, “<code class="literal">LINEAR HASH</code> Partitioning”</a>
</p></li></ul></div><p>
For more complex examples of date-based partitioning, see:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="partitioning.html#partitioning-pruning" title="18.4. Partition Pruning">Section 18.4, “Partition Pruning”</a>
</p></li><li><p>
<a href="partitioning.html#partitioning-subpartitions" title="18.2.5. Subpartitioning">Section 18.2.5, “Subpartitioning”</a>
</p></li></ul></div><p>
MySQL partitioning is optimized for use with the
<a href="functions.html#function_to-days"><code class="literal">TO_DAYS()</code></a> and
<a href="functions.html#function_year"><code class="literal">YEAR()</code></a> functions. However, you can
use other date and time functions that return an integer or
<code class="literal">NULL</code>, such as
<a href="functions.html#function_weekday"><code class="literal">WEEKDAY()</code></a>,
<a href="functions.html#function_dayofyear"><code class="literal">DAYOFYEAR()</code></a>, or
<a href="functions.html#function_month"><code class="literal">MONTH()</code></a>. See
<a href="functions.html#date-and-time-functions" title="11.6. Date and Time Functions">Section 11.6, “Date and Time Functions”</a>, for more information
about such functions.
</p><p>
It is important to remember — regardless of the type of
partitioning that you use — that partitions are always
numbered automatically and in sequence when created, starting with
<code class="literal">0</code>. When a new row is inserted into a
partitioned table, it is these partition numbers that are used in
identifying the correct partition. For example, if your table uses
4 partitions, these partitions are numbered <code class="literal">0</code>,
<code class="literal">1</code>, <code class="literal">2</code>, and
<code class="literal">3</code>. For the <code class="literal">RANGE</code> and
<code class="literal">LIST</code> partitioning types, it is necessary to
ensure that there is a partition defined for each partition
number. For <code class="literal">HASH</code> partitioning, the user
function employed must return an integer value greater than
<code class="literal">0</code>. For <code class="literal">KEY</code> partitioning,
this issue is taken care of automatically by the hashing function
which the MySQL server employs internally.
</p><p>
Names of partitions generally follow the rules governing other
MySQL identifiers, such as those for tables and databases.
However, you should note that partition names are not
case-sensitive. For example, the following
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement fails as
shown:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t2 (val INT)</code></strong>
-> <strong class="userinput"><code>PARTITION BY LIST(val)(</code></strong>
-> <strong class="userinput"><code>PARTITION mypart VALUES IN (1,3,5),</code></strong>
-> <strong class="userinput"><code>PARTITION MyPart VALUES IN (2,4,6)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
ERROR 1488 (HY000): Duplicate partition name mypart
</pre><p>
Failure occurs because MySQL sees no difference between the
partition names <code class="literal">mypart</code> and
<code class="literal">MyPart</code>.
</p><p>
When you specify the number of partitions for the table, this must
be expressed as a positive, nonzero integer literal with no
leading zeroes, and may not be an expression such as
<code class="literal">0.8E+01</code> or <code class="literal">6-2</code>, even if it
evaluates to an integer value. (Beginning with MySQL 5.1.12,
decimal fractions are no longer truncated, but instead are
disallowed entirely.)
</p><p>
In the sections that follow, we do not necessarily provide all
possible forms for the syntax that can be used for creating each
partition type; this information may be found in
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax">Section 12.1.17, “<code class="literal">CREATE TABLE</code> Syntax”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-range"></a>18.2.1. <code class="literal">RANGE</code> Partitioning</h3></div></div></div><a class="indexterm" name="id4820466"></a><a class="indexterm" name="id4820478"></a><p>
A table that is partitioned by range is partitioned in such a
way that each partition contains rows for which the partitioning
expression value lies within a given range. Ranges should be
contiguous but not overlapping, and are defined using the
<code class="literal">VALUES LESS THAN</code> operator. For the next few
examples, suppose that you are creating a table such as the
following to hold personnel records for a chain of 20 video
stores, numbered 1 through 20:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
);
</pre><p>
This table can be partitioned by range in a number of ways,
depending on your needs. One way would be to use the
<code class="literal">store_id</code> column. For instance, you might
decide to partition the table 4 ways by adding a
<code class="literal">PARTITION BY RANGE</code> clause as shown here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
</pre><p>
In this partitioning scheme, all rows corresponding to employees
working at stores 1 through 5 are stored in partition
<code class="literal">p0</code>, to those employed at stores 6 through 10
are stored in partition <code class="literal">p1</code>, and so on. Note
that each partition is defined in order, from lowest to highest.
This is a requirement of the <code class="literal">PARTITION BY
RANGE</code> syntax; you can think of it as being analogous
to a series of <code class="literal">if ... elseif ...</code> statements
in C or Java in this regard.
</p><p>
It is easy to determine that a new row containing the data
<code class="literal">(72, 'Michael', 'Widenius', '1998-06-25', NULL,
13)</code> is inserted into partition <code class="literal">p2</code>,
but what happens when your chain adds a
21<sup>st</sup> store? Under this scheme, there
is no rule that covers a row whose <code class="literal">store_id</code>
is greater than 20, so an error results because the server does
not know where to place it. You can keep this from occurring by
using a “<span class="quote">catchall</span>” <code class="literal">VALUES LESS
THAN</code> clause in the <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement that provides for all values greater
than highest value explicitly named:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
<span class="emphasis"><em>PARTITION p3 VALUES LESS THAN MAXVALUE</em></span>
);
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Another way to avoid an error when no matching value is found
is to use the <code class="literal">IGNORE</code> keyword as part of the
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statement. For an
example, see <a href="partitioning.html#partitioning-list" title="18.2.2. LIST Partitioning">Section 18.2.2, “<code class="literal">LIST</code> Partitioning”</a>. Also see
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax">Section 12.2.5, “<code class="literal">INSERT</code> Syntax”</a>, for general information about
<code class="literal">IGNORE</code>.
</p></div><p>
<code class="literal">MAXVALUE</code> represents an integer value that is
always greater than the largest possible integer value (in
mathematical language, it serves as a <em class="firstterm">least upper
bound</em>). Now, any rows whose
<code class="literal">store_id</code> column value is greater than or
equal to 16 (the highest value defined) are stored in partition
<code class="literal">p3</code>. At some point in the future — when
the number of stores has increased to 25, 30, or more —
you can use an <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
statement to add new partitions for stores 21-25, 26-30, and so
on (see <a href="partitioning.html#partitioning-management" title="18.3. Partition Management">Section 18.3, “Partition Management”</a>, for details
of how to do this).
</p><p>
In much the same fashion, you could partition the table based on
employee job codes — that is, based on ranges of
<code class="literal">job_code</code> column values. For example —
assuming that two-digit job codes are used for regular
(in-store) workers, three-digit codes are used for office and
support personnel, and four-digit codes are used for management
positions — you could create the partitioned table using
the following:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
</pre><p>
In this instance, all rows relating to in-store workers would be
stored in partition <code class="literal">p0</code>, those relating to
office and support staff in <code class="literal">p1</code>, and those
relating to managers in partition <code class="literal">p2</code>.
</p><p>
It is also possible to use an expression in <code class="literal">VALUES LESS
THAN</code> clauses. However, MySQL must be able to evaluate
the expression's return value as part of a <code class="literal">LESS
THAN</code> (<code class="literal"><</code>) comparison.
</p><a class="indexterm" name="id4820771"></a><p>
Rather than splitting up the table data according to store
number, you can use an expression based on one of the two
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> columns instead. For
example, let us suppose that you wish to partition based on the
year that each employee left the company; that is, the value of
<a href="functions.html#function_year"><code class="literal">YEAR(separated)</code></a>. An example of a
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement that
implements such a partitioning scheme is shown here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
</pre><p>
In this scheme, for all employees who left before 1991, the rows
are stored in partition <code class="literal">p0</code>; for those who
left in the years 1991 through 1995, in <code class="literal">p1</code>;
for those who left in the years 1996 through 2000, in
<code class="literal">p2</code>; and for any workers who left after the
year 2000, in <code class="literal">p3</code>.
</p><p>
Range partitioning is particularly useful when:
</p><div class="itemizedlist"><ul type="disc"><li><p>
You want or need to delete “<span class="quote">old</span>” data. If you
are using the partitioning scheme shown immediately above,
you can simply use <code class="literal">ALTER TABLE employees DROP
PARTITION p0;</code> to delete all rows relating to
employees who stopped working for the firm prior to 1991.
(See <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax">Section 12.1.7, “<code class="literal">ALTER TABLE</code> Syntax”</a>, and
<a href="partitioning.html#partitioning-management" title="18.3. Partition Management">Section 18.3, “Partition Management”</a>, for more
information.) For a table with a great many rows, this can
be much more efficient than running a
<a href="sql-syntax.html#delete" title="12.2.2. DELETE Syntax"><code class="literal">DELETE</code></a> query such as
<code class="literal">DELETE FROM employees WHERE YEAR(separated) <=
1990;</code>.
</p></li><li><p>
You want to use a column containing date or time values, or
containing values arising from some other series.
</p></li><li><p>
You frequently run queries that depend directly on the
column used for partitioning the table. For example, when
executing a query such as <code class="literal">EXPLAIN PARTITIONS SELECT
COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01'
AND '2000-12-31' GROUP BY store_id;</code>, MySQL can
quickly determine that only partition <code class="literal">p2</code>
needs to be scanned because the remaining partitions cannot
contain any records satisfying the <code class="literal">WHERE</code>
clause. See <a href="partitioning.html#partitioning-pruning" title="18.4. Partition Pruning">Section 18.4, “Partition Pruning”</a>, for more
information about how this is accomplished.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-list"></a>18.2.2. <code class="literal">LIST</code> Partitioning</h3></div></div></div><a class="indexterm" name="id4820954"></a><a class="indexterm" name="id4820966"></a><p>
List partitioning in MySQL is similar to range partitioning in
many ways. As in partitioning by <code class="literal">RANGE</code>, each
partition must be explicitly defined. The chief difference is
that, in list partitioning, each partition is defined and
selected based on the membership of a column value in one of a
set of value lists, rather than in one of a set of contiguous
ranges of values. This is done by using <code class="literal">PARTITION BY
LIST(<em class="replaceable"><code>expr</code></em>)</code> where
<em class="replaceable"><code>expr</code></em> is a column value or an
expression based on a column value and returning an integer
value, and then defining each partition by means of a
<code class="literal">VALUES IN
(<em class="replaceable"><code>value_list</code></em>)</code>, where
<em class="replaceable"><code>value_list</code></em> is a comma-separated list
of integers.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In MySQL 5.1, it is possible to match against
only a list of integers (and possibly <code class="literal">NULL</code>
— see <a href="partitioning.html#partitioning-handling-nulls" title="18.2.6. How MySQL Partitioning Handles NULL">Section 18.2.6, “How MySQL Partitioning Handles <code class="literal">NULL</code>”</a>)
when partitioning by <code class="literal">LIST</code>.
</p></div><p>
Unlike the case with partitions defined by range, list
partitions do not need to be declared in any particular order.
For more detailed syntactical information, see
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax">Section 12.1.17, “<code class="literal">CREATE TABLE</code> Syntax”</a>.
</p><p>
For the examples that follow, we assume that the basic
definition of the table to be partitioned is provided by the
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement shown
here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
);
</pre><p>
(This is the same table used as a basis for the examples in
<a href="partitioning.html#partitioning-range" title="18.2.1. RANGE Partitioning">Section 18.2.1, “<code class="literal">RANGE</code> Partitioning”</a>.)
</p><p>
Suppose that there are 20 video stores distributed among 4
franchises as shown in the following table.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Region</strong></span></td><td><span class="bold"><strong>Store ID Numbers</strong></span></td></tr><tr><td>North</td><td>3, 5, 6, 9, 17</td></tr><tr><td>East</td><td>1, 2, 10, 11, 19, 20</td></tr><tr><td>West</td><td>4, 12, 13, 14, 18</td></tr><tr><td>Central</td><td>7, 8, 15, 16</td></tr></tbody></table></div><p>
To partition this table in such a way that rows for stores
belonging to the same region are stored in the same partition,
you could use the <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
statement shown here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
</pre><p>
This makes it easy to add or drop employee records relating to
specific regions to or from the table. For instance, suppose
that all stores in the West region are sold to another company.
All rows relating to employees working at stores in that region
can be deleted with the query <code class="literal">ALTER TABLE employees
DROP PARTITION pWest;</code>, which can be executed much more
efficiently than the equivalent
<a href="sql-syntax.html#delete" title="12.2.2. DELETE Syntax"><code class="literal">DELETE</code></a> statement <code class="literal">DELETE
FROM employees WHERE store_id IN (4,12,13,14,18);</code>.
</p><p>
As with <code class="literal">RANGE</code> partitioning, it is possible to
combine <code class="literal">LIST</code> partitioning with partitioning
by hash or key to produce a composite partitioning
(subpartitioning). See
<a href="partitioning.html#partitioning-subpartitions" title="18.2.5. Subpartitioning">Section 18.2.5, “Subpartitioning”</a>.
</p><p>
Unlike the case with <code class="literal">RANGE</code> partitioning,
there is no “<span class="quote">catch-all</span>” such as
<code class="literal">MAXVALUE</code>; all expected values for the
partitioning expression should be covered in <code class="literal">PARTITION
... VALUES IN (...)</code> clauses. An
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statement containing an
unmatched partitioning column value fails with an error, as
shown in this example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE h2 (</code></strong>
-> <strong class="userinput"><code>c1 INT,</code></strong>
-> <strong class="userinput"><code>c2 INT</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY LIST(c1) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES IN (1, 4, 7),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES IN (2, 5, 8)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.11 sec)
mysql> <strong class="userinput"><code>INSERT INTO h2 VALUES (3, 5);</code></strong>
<span class="errortext">ERROR 1525 (HY000): Table has no partition for value 3</span>
</pre><p>
When inserting multiple rows using a single
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statement, any rows coming
before the row containing the unmatched value are inserted, but
any coming after it are not:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM h2;</code></strong>
Empty set (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO h2 VALUES (4, 7), (3, 5), (6, 0);</code></strong>
<span class="errortext">ERROR 1525 (HY000): Table has no partition for value 3</span>
mysql> <strong class="userinput"><code>SELECT * FROM h2;</code></strong>
+------+------+
| c1 | c2 |
+------+------+
| 4 | 7 |
+------+------+
1 row in set (0.00 sec)
</pre><p>
You can cause this type of error to be ignored by using the
<code class="literal">IGNORE</code> key word. If you do so, rows
containing unmatched partitioning column values are not
inserted, but any rows with matching values
<span class="emphasis"><em>are</em></span> inserted, and no errors are reported:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>TRUNCATE h2;</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM h2;</code></strong>
Empty set (0.00 sec)
mysql> <strong class="userinput"><code>INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);</code></strong>
Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0
mysql> <strong class="userinput"><code>SELECT * FROM h2;</code></strong>
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)
</pre><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-hash"></a>18.2.3. <code class="literal">HASH</code> Partitioning</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="partitioning.html#partitioning-linear-hash">18.2.3.1. <code class="literal">LINEAR HASH</code> Partitioning</a></span></dt></dl></div><a class="indexterm" name="id4821423"></a><a class="indexterm" name="id4821435"></a><p>
Partitioning by <code class="literal">HASH</code> is used primarily to
ensure an even distribution of data among a predetermined number
of partitions. With range or list partitioning, you must specify
explicitly into which partition a given column value or set of
column values is to be stored; with hash partitioning, MySQL
takes care of this for you, and you need only specify a column
value or expression based on a column value to be hashed and the
number of partitions into which the partitioned table is to be
divided.
</p><p>
To partition a table using <code class="literal">HASH</code> partitioning,
it is necessary to append to the <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement a <code class="literal">PARTITION BY HASH
(<em class="replaceable"><code>expr</code></em>)</code> clause, where
<em class="replaceable"><code>expr</code></em> is an expression that returns an
integer. This can simply be the name of a column whose type is
one of MySQL's integer types. In addition, you will most likely
want to follow this with a <code class="literal">PARTITIONS
<em class="replaceable"><code>num</code></em></code> clause, where
<em class="replaceable"><code>num</code></em> is a positive integer
representing the number of partitions into which the table is to
be divided.
</p><p>
For example, the following statement creates a table that uses
hashing on the <code class="literal">store_id</code> column and is divided
into 4 partitions:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
</pre><p>
If you do not include a <code class="literal">PARTITIONS</code> clause,
the number of partitions defaults to <code class="literal">1</code>.
</p><p>
Using the <code class="literal">PARTITIONS</code> keyword without a number
following it results in a syntax error.
</p><a class="indexterm" name="id4821547"></a><p>
You can also use an SQL expression that returns an integer for
<em class="replaceable"><code>expr</code></em>. For instance, you might want to
partition based on the year in which an employee was hired. This
can be done as shown here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
</pre><p>
<em class="replaceable"><code>expr</code></em> must return a nonconstant,
nonrandom integer value (in other words, it should be varying
but deterministic), and must not contain any prohibited
constructs as described in
<a href="partitioning.html#partitioning-limitations" title="18.5. Restrictions and Limitations on Partitioning">Section 18.5, “Restrictions and Limitations on Partitioning”</a>. You should also keep
in mind that this expression is evaluated each time a row is
inserted or updated (or possibly deleted); this means that very
complex expressions may give rise to performance issues,
particularly when performing operations (such as batch inserts)
that affect a great many rows at one time.
</p><p>
The most efficient hashing function is one which operates upon a
single table column and whose value increases or decreases
consistently with the column value, as this allows for
“<span class="quote">pruning</span>” on ranges of partitions. That is, the
more closely that the expression varies with the value of the
column on which it is based, the more efficiently MySQL can use
the expression for hash partitioning.
</p><p>
For example, where <code class="literal">date_col</code> is a column of
type <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>, then the expression
<a href="functions.html#function_to-days"><code class="literal">TO_DAYS(date_col)</code></a> is said to vary
directly with the value of <code class="literal">date_col</code>, because
for every change in the value of <code class="literal">date_col</code>,
the value of the expression changes in a consistent manner. The
variance of the expression
<a href="functions.html#function_year"><code class="literal">YEAR(date_col)</code></a> with respect to
<code class="literal">date_col</code> is not quite as direct as that of
<a href="functions.html#function_to-days"><code class="literal">TO_DAYS(date_col)</code></a>, because not
every possible change in <code class="literal">date_col</code> produces an
equivalent change in
<a href="functions.html#function_year"><code class="literal">YEAR(date_col)</code></a>. Even so,
<a href="functions.html#function_year"><code class="literal">YEAR(date_col)</code></a> is a good
candidate for a hashing function, because it varies directly
with a portion of <code class="literal">date_col</code> and there is no
possible change in <code class="literal">date_col</code> that produces a
disproportionate change in
<a href="functions.html#function_year"><code class="literal">YEAR(date_col)</code></a>.
</p><p>
By way of contrast, suppose that you have a column named
<code class="literal">int_col</code> whose type is
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a>. Now consider the expression
<a href="functions.html#function_pow"><code class="literal">POW(5-int_col,3) + 6</code></a>. This would
be a poor choice for a hashing function because a change in the
value of <code class="literal">int_col</code> is not guaranteed to produce
a proportional change in the value of the expression. Changing
the value of <code class="literal">int_col</code> by a given amount can
produce by widely different changes in the value of the
expression. For example, changing <code class="literal">int_col</code>
from <code class="literal">5</code> to <code class="literal">6</code> produces a
change of <code class="literal">-1</code> in the value of the expression,
but changing the value of <code class="literal">int_col</code> from
<code class="literal">6</code> to <code class="literal">7</code> produces a change
of <code class="literal">-7</code> in the expression value.
</p><p>
In other words, the more closely the graph of the column value
<span class="foreignphrase"><em class="foreignphrase">versus</em></span> the value of the
expression follows a straight line as traced by the equation
<code class="literal">y=<em class="replaceable"><code>n</code></em>x</code> where
<em class="replaceable"><code>n</code></em> is some nonzero constant, the
better the expression is suited to hashing. This has to do with
the fact that the more nonlinear an expression is, the more
uneven the distribution of data among the partitions it tends to
produce.
</p><p>
In theory, pruning is also possible for expressions involving
more than one column value, but determining which of such
expressions are suitable can be quite difficult and
time-consuming. For this reason, the use of hashing expressions
involving multiple columns is not particularly recommended.
</p><p>
When <code class="literal">PARTITION BY HASH</code> is used, MySQL
determines which partition of <em class="replaceable"><code>num</code></em>
partitions to use based on the modulus of the result of the user
function. In other words, for an expression
<em class="replaceable"><code>expr</code></em>, the partition in which the
record is stored is partition number
<em class="replaceable"><code>N</code></em>, where
<code class="literal"><em class="replaceable"><code>N</code></em> =
MOD(<em class="replaceable"><code>expr</code></em>,
<em class="replaceable"><code>num</code></em>)</code>. For example, suppose
table <code class="literal">t1</code> is defined as follows, so that it
has 4 partitions:
</p><pre class="programlisting">CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
</pre><p>
If you insert a record into <code class="literal">t1</code> whose
<code class="literal">col3</code> value is
<code class="literal">'2005-09-15'</code>, then the partition in which it
is stored is determined as follows:
</p><pre class="programlisting">MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1
</pre><p>
MySQL 5.1 also supports a variant of
<code class="literal">HASH</code> partitioning known as <em class="firstterm">linear
hashing</em> which employs a more complex algorithm for
determining the placement of new rows inserted into the
partitioned table. See
<a href="partitioning.html#partitioning-linear-hash" title="18.2.3.1. LINEAR HASH Partitioning">Section 18.2.3.1, “<code class="literal">LINEAR HASH</code> Partitioning”</a>, for a description of
this algorithm.
</p><p>
The user function is evaluated each time a record is inserted or
updated. It may also — depending on the circumstances
— be evaluated when records are deleted.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
If a table to be partitioned has a <code class="literal">UNIQUE</code>
key, then any columns supplied as arguments to the
<code class="literal">HASH</code> user function or to the
<code class="literal">KEY</code>'s
<em class="replaceable"><code>column_list</code></em> must be part of that
key.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="partitioning-linear-hash"></a>18.2.3.1. <code class="literal">LINEAR HASH</code> Partitioning</h4></div></div></div><a class="indexterm" name="id4821963"></a><a class="indexterm" name="id4821975"></a><p>
MySQL also supports linear hashing, which differs from regular
hashing in that linear hashing utilizes a linear powers-of-two
algorithm whereas regular hashing employs the modulus of the
hashing function's value.
</p><p>
Syntactically, the only difference between linear-hash
partitioning and regular hashing is the addition of the
<code class="literal">LINEAR</code> keyword in the <code class="literal">PARTITION
BY</code> clause, as shown here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
</pre><p>
Given an expression <em class="replaceable"><code>expr</code></em>, the
partition in which the record is stored when linear hashing is
used is partition number <em class="replaceable"><code>N</code></em> from
among <em class="replaceable"><code>num</code></em> partitions, where
<em class="replaceable"><code>N</code></em> is derived according to the
following algorithm:
</p><div class="orderedlist"><ol type="1"><li><p>
Find the next power of 2 greater than
<em class="replaceable"><code>num</code></em>. We call this value
<em class="replaceable"><code>V</code></em>; it can be calculated as:
</p><pre class="programlisting"><em class="replaceable"><code>V</code></em> = POWER(2, CEILING(LOG(2, <em class="replaceable"><code>num</code></em>)))
</pre><p>
(For example, suppose that <em class="replaceable"><code>num</code></em>
is 13. Then <a href="functions.html#function_log"><code class="literal">LOG(2,13)</code></a> is
3.7004397181411.
<a href="functions.html#function_ceiling"><code class="literal">CEILING(3.7004397181411)</code></a> is
4, and <em class="replaceable"><code>V</code></em> =
<a href="functions.html#function_power"><code class="literal">POWER(2,4)</code></a>, which is 16.)
</p></li><li><p>
Set <em class="replaceable"><code>N</code></em> =
<em class="replaceable"><code>F</code></em>(<em class="replaceable"><code>column_list</code></em>)
& (<em class="replaceable"><code>V</code></em> - 1).
</p></li><li><p>
While <em class="replaceable"><code>N</code></em> >=
<em class="replaceable"><code>num</code></em>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Set <em class="replaceable"><code>V</code></em> =
CEIL(<em class="replaceable"><code>V</code></em> / 2)
</p></li><li><p>
Set <em class="replaceable"><code>N</code></em> =
<em class="replaceable"><code>N</code></em> &
(<em class="replaceable"><code>V</code></em> - 1)
</p></li></ul></div></li></ol></div><p>
For example, suppose that the table <code class="literal">t1</code>,
using linear hash partitioning and having 6 partitions, is
created using this statement:
</p><pre class="programlisting">CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
</pre><p>
Now assume that you want to insert two records into
<code class="literal">t1</code> having the <code class="literal">col3</code>
column values <code class="literal">'2003-04-14'</code> and
<code class="literal">'1998-10-19'</code>. The partition number for the
first of these is determined as follows:
</p><pre class="programlisting"><em class="replaceable"><code>V</code></em> = POWER(2, CEILING( LOG(2,6) )) = 8
<em class="replaceable"><code>N</code></em> = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(<span class="emphasis"><em>3 >= 6 is FALSE: record stored in partition #3</em></span>)
</pre><p>
The number of the partition where the second record is stored
is calculated as shown here:
</p><pre class="programlisting"><em class="replaceable"><code>V</code></em> = 8
<em class="replaceable"><code>N</code></em> = YEAR('1998-10-19') & (8-1)
= 1998 & 7
= 6
(<span class="emphasis"><em>6 >= 6 is TRUE: additional step required</em></span>)
<em class="replaceable"><code>N</code></em> = 6 & CEILING(8 / 2)
= 6 & 3
= 2
(<span class="emphasis"><em>2 >= 6 is FALSE: record stored in partition #2</em></span>)
</pre><p>
</p><p>
The advantage in partitioning by linear hash is that the
adding, dropping, merging, and splitting of partitions is made
much faster, which can be beneficial when dealing with tables
containing extremely large amounts (terabytes) of data. The
disadvantage is that data is less likely to be evenly
distributed between partitions as compared with the
distribution obtained using regular hash partitioning.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-key"></a>18.2.4. <code class="literal">KEY</code> Partitioning</h3></div></div></div><a class="indexterm" name="id4822274"></a><a class="indexterm" name="id4822286"></a><p>
Partitioning by key is similar to partitioning by hash, except
that where hash partitioning employs a user-defined expression,
the hashing function for key partitioning is supplied by the
MySQL server. MySQL Cluster uses
<a href="functions.html#function_md5"><code class="literal">MD5()</code></a> for this purpose; for
tables using other storage engines, the server employs its own
internal hashing function which is based on the same algorithm
as <a href="functions.html#function_password"><code class="literal">PASSWORD()</code></a>.
</p><p>
The syntax rules for <code class="literal">CREATE TABLE ... PARTITION BY
KEY</code> are similar to those for creating a table that is
partitioned by hash. The major differences are that:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">KEY</code> is used rather than
<code class="literal">HASH</code>.
</p></li><li><p>
<code class="literal">KEY</code> takes only a list of one or more
column names. Beginning with MySQL 5.1.5, the column or
columns used as the partitioning key must comprise part or
all of the table's primary key, if the table has one.
</p><p>
Beginning with MySQL 5.1.6, <code class="literal">KEY</code> takes a
list of zero or more column names. Where no column name is
specified as the partitioning key, the table's primary key
is used, if there is one. For example, the following
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement is
valid in MySQL 5.1.6 or later:
</p><pre class="programlisting">CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
</pre><p>
If there is no primary key but there is a unique key, then
the unique key is used for the partitioning key:
</p><pre class="programlisting">CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
</pre><p>
However, if the unique key column were not defined as
<code class="literal">NOT NULL</code>, then the previous statement
would fail.
</p><p>
In both of these cases, the partitioning key is the
<code class="literal">id</code> column, even though it is not shown in
the output of <a href="sql-syntax.html#show-create-table" title="12.5.5.12. SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE
TABLE</code></a> or in the
<code class="literal">PARTITION_EXPRESSION</code> column of the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">INFORMATION_SCHEMA.PARTITIONS</code></a>
table.
</p><p>
Unlike the case with other partitioning types, columns used
for partitioning by <code class="literal">KEY</code> are not
restricted to integer or <code class="literal">NULL</code> values. For
example, the following <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement is valid:
</p><pre class="programlisting">CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;
</pre><p>
The preceding statement would <span class="emphasis"><em>not</em></span> be
valid, were a different partitioning type to be specified.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In this case, simply using <code class="literal">PARTITION BY
KEY()</code> would also be valid and have the same
effect as <code class="literal">PARTITION BY KEY(s1)</code>, since
<code class="literal">s1</code> is the table's primary key.
</p></div><p>
</p><p>
For additional information about this issue, see
<a href="partitioning.html#partitioning-limitations" title="18.5. Restrictions and Limitations on Partitioning">Section 18.5, “Restrictions and Limitations on Partitioning”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Also beginning with MySQL 5.1.6, tables using the
<code class="literal">NDBCLUSTER</code> storage engine are
implicitly partitioned by <code class="literal">KEY</code>, again
using the table's primary key as the partitioning
key. In the event that the MySQL Cluster table has no
explicit primary key, the “<span class="quote">hidden</span>” primary
key generated by the
<code class="literal">NDBCLUSTER</code> storage engine for
each MySQL Cluster table is used as the partitioning key.
</p><p>
Beginning with MySQL Cluster NDB 6.2.18, MySQL Cluster NDB
6.3.25, and MySQL Cluster NDB 7.0.6, if you define an
explicit partitioning scheme for an
<code class="literal">NDBCLUSTER</code> table, the table
must have an explicit primary key, and any columns used in
the partitioning expression must be part of this key.
However, if the table uses an “<span class="quote">empty</span>”
partitioning expression — that is,
<code class="literal">PARTITION BY KEY()</code> with no column
references — then no explicit primary key is
required.
</p></div><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
For a key-partitioned table using any MySQL storage engine
other than <code class="literal">NDBCLUSTER</code>, you
cannot execute an <code class="literal">ALTER TABLE DROP PRIMARY
KEY</code>, as doing so generates the error
<span class="errortext">ERROR 1466 (HY000): Field in list of fields for
partition function not found in table</span>. This is
not an issue for MySQL Cluster tables which are
partitioned by <code class="literal">KEY</code>; in such cases, the
table is reorganized using the “<span class="quote">hidden</span>”
primary key as the table's new partitioning key. See
<a href="mysql-cluster.html" title="Chapter 17. MySQL Cluster NDB 6.X/7.X">Chapter 17, <i>MySQL Cluster NDB 6.X/7.X</i></a>.
</p></div></li></ul></div><a class="indexterm" name="id4822617"></a><a class="indexterm" name="id4822629"></a><p>
It is also possible to partition a table by linear key. Here is
a simple example:
</p><pre class="programlisting">CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
</pre><p>
Using <code class="literal">LINEAR</code> has the same effect on
<code class="literal">KEY</code> partitioning as it does on
<code class="literal">HASH</code> partitioning, with the partition number
being derived using a powers-of-two algorithm rather than modulo
arithmetic. See <a href="partitioning.html#partitioning-linear-hash" title="18.2.3.1. LINEAR HASH Partitioning">Section 18.2.3.1, “<code class="literal">LINEAR HASH</code> Partitioning”</a>, for
a description of this algorithm and its implications.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-subpartitions"></a>18.2.5. Subpartitioning</h3></div></div></div><a class="indexterm" name="id4822686"></a><a class="indexterm" name="id4822695"></a><p>
Subpartitioning — also known as <em class="firstterm">composite
partitioning</em> — is the further division of each
partition in a partitioned table. For example, consider the
following <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement:
</p><pre class="programlisting">CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
</pre><p>
Table <code class="literal">ts</code> has 3 <code class="literal">RANGE</code>
partitions. Each of these partitions —
<code class="literal">p0</code>, <code class="literal">p1</code>, and
<code class="literal">p2</code> — is further divided into 2
subpartitions. In effect, the entire table is divided into
<code class="literal">3 * 2 = 6</code> partitions. However, due to the
action of the <code class="literal">PARTITION BY RANGE</code> clause, the
first 2 of these store only those records with a value less than
1990 in the <code class="literal">purchased</code> column.
</p><a class="indexterm" name="id4822782"></a><p>
In MySQL 5.1, it is possible to subpartition tables
that are partitioned by <code class="literal">RANGE</code> or
<code class="literal">LIST</code>. Subpartitions may use either
<code class="literal">HASH</code> or <code class="literal">KEY</code> partitioning.
This is also known as <em class="firstterm">composite
partitioning</em>.
</p><a class="indexterm" name="id4822823"></a><p>
It is also possible to define subpartitions explicitly using
<code class="literal">SUBPARTITION</code> clauses to specify options for
individual subpartitions. For example, a more verbose fashion of
creating the same table <code class="literal">ts</code> as shown in the
previous example would be:
</p><pre class="programlisting">CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
</pre><p>
Some syntactical items of note:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Each partition must have the same number of subpartitions.
</p></li><li><p>
If you explicitly define any subpartitions using
<code class="literal">SUBPARTITION</code> on any partition of a
partitioned table, you must define them all. In other words,
the following statement will fail:
</p><pre class="programlisting">CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
);
</pre><p>
This statement would still fail even if it included a
<code class="literal">SUBPARTITIONS 2</code> clause.
</p></li><li><p>
Each <code class="literal">SUBPARTITION</code> clause must include (at
a minimum) a name for the subpartition. Otherwise, you may
set any desired option for the subpartition or allow it to
assume its default setting for that option.
</p></li><li><p>
In MySQL 5.1.7 and earlier, names of subpartitions were
required to be unique within each partition, but did not
have to be unique within the table as a whole. Beginning
with MySQL 5.1.8, subpartition names must be unique across
the entire table. For example, the following
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement is
valid in MySQL 5.1.8 and later:
</p><pre class="programlisting">CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
</pre><p>
(The previous statement is also valid for versions of MySQL
prior to 5.1.8.)
</p></li></ul></div><p>
Subpartitions can be used with especially large tables to
distribute data and indexes across many disks. Suppose that you
have 6 disks mounted as <code class="filename">/disk0</code>,
<code class="filename">/disk1</code>, <code class="filename">/disk2</code>, and so
on. Now consider the following example:
</p><pre class="programlisting">CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1
DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3
DATA DIRECTORY = '/disk3/data'
INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
)
);
</pre><p>
In this case, a separate disk is used for the data and for the
indexes of each <code class="literal">RANGE</code>. Many other variations
are possible; another example might be:
</p><pre class="programlisting">CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0a
DATA DIRECTORY = '/disk0'
INDEX DIRECTORY = '/disk1',
SUBPARTITION s0b
DATA DIRECTORY = '/disk2'
INDEX DIRECTORY = '/disk3'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s1a
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s1b
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2a,
SUBPARTITION s2b
)
);
</pre><p>
Here, the storage is as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Rows with <code class="literal">purchased</code> dates from before
1990 take up a vast amount of space, so are split up 4 ways,
with a separate disk dedicated to the data and to the
indexes for each of the two subpartitions
(<code class="literal">s0a</code> and <code class="literal">s0b</code>) making
up partition <code class="literal">p0</code>. In other words:
</p><div class="itemizedlist"><ul type="circle"><li><p>
The data for subpartition <code class="literal">s0a</code> is
stored on <code class="filename">/disk0</code>.
</p></li><li><p>
The indexes for subpartition <code class="literal">s0a</code> are
stored on <code class="filename">/disk1</code>.
</p></li><li><p>
The data for subpartition <code class="literal">s0b</code> is
stored on <code class="filename">/disk2</code>.
</p></li><li><p>
The indexes for subpartition <code class="literal">s0b</code> are
stored on <code class="filename">/disk3</code>.
</p></li></ul></div></li><li><p>
Rows containing dates ranging from 1990 to 1999 (partition
<code class="literal">p1</code>) do not require as much room as those
from before 1990. These are split between 2 disks
(<code class="filename">/disk4</code> and
<code class="filename">/disk5</code>) rather than 4 disks as with the
legacy records stored in <code class="literal">p0</code>:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Data and indexes belonging to <code class="literal">p1</code>'s
first subpartition (<code class="literal">s1a</code>) are stored
on <code class="filename">/disk4</code> — the data in
<code class="filename">/disk4/data</code>, and the indexes in
<code class="filename">/disk4/idx</code>.
</p></li><li><p>
Data and indexes belonging to <code class="literal">p1</code>'s
second subpartition (<code class="literal">s1b</code>) are stored
on <code class="filename">/disk5</code> — the data in
<code class="filename">/disk5/data</code>, and the indexes in
<code class="filename">/disk5/idx</code>.
</p></li></ul></div></li><li><p>
Rows reflecting dates from the year 2000 to the present
(partition <code class="literal">p2</code>) do not take up as much
space as required by either of the two previous ranges.
Currently, it is sufficient to store all of these in the
default location.
</p><p>
In future, when the number of purchases for the decade
beginning with the year 2000 grows to a point where the
default location no longer provides sufficient space, the
corresponding rows can be moved using an <code class="literal">ALTER
TABLE ... REORGANIZE PARTITION</code> statement. See
<a href="partitioning.html#partitioning-management" title="18.3. Partition Management">Section 18.3, “Partition Management”</a>, for an
explanation of how this can be done.
</p></li></ul></div><p>
Beginning with MySQL 5.1.18, the <code class="literal">DATA
DIRECTORY</code> and <code class="literal">INDEX DIRECTORY</code>
options are disallowed when the
<a href="server-administration.html#sqlmode_no_dir_in_create"><code class="literal">NO_DIR_IN_CREATE</code></a> server SQL
mode is in effect. This is true for partitions and
subpartitions.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-handling-nulls"></a>18.2.6. How MySQL Partitioning Handles <code class="literal">NULL</code></h3></div></div></div><p>
Partitioning in MySQL does nothing to disallow
<code class="literal">NULL</code> as the value of a partitioning
expression, whether it is a column value or the value of a
user-supplied expression. Even though it is permitted to use
<code class="literal">NULL</code> as the value of an expression that must
otherwise yield an integer, it is important to keep in mind that
<code class="literal">NULL</code> is not a number. Beginning with MySQL
5.1.8, the partitioning implementation treats
<code class="literal">NULL</code> as being less than any
non-<code class="literal">NULL</code> value, just as <code class="literal">ORDER
BY</code> does.
</p><p>
This means that treatment of <code class="literal">NULL</code> varies
between partitioning of different types, and may produce
behavior which you do not expect if you are not prepared for it.
This being the case, we discuss in this section how each MySQL
partitioning type handles <code class="literal">NULL</code> values when
determining the partition in which a row should be stored, and
provide examples for each.
</p><p><b>Handling of <code class="literal">NULL</code> with <code class="literal">RANGE</code>
partitioning. </b>
If you insert a row into a table partitioned by
<code class="literal">RANGE</code> such that the column value used to
determine the partition is <code class="literal">NULL</code>, the row is
inserted into the lowest partition. For example, consider
these two tables in a database named <code class="literal">p</code>,
created as follows:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t1 (</code></strong>
-> <strong class="userinput"><code>c1 INT,</code></strong>
-> <strong class="userinput"><code>c2 VARCHAR(20)</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY RANGE(c1) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES LESS THAN (0),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES LESS THAN (10),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES LESS THAN MAXVALUE</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.09 sec)
mysql> <strong class="userinput"><code>CREATE TABLE t2 (</code></strong>
-> <strong class="userinput"><code>c1 INT,</code></strong>
-> <strong class="userinput"><code>c2 VARCHAR(20)</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY RANGE(c1) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES LESS THAN (-5),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES LESS THAN (0),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES LESS THAN (10),</code></strong>
-> <strong class="userinput"><code>PARTITION p3 VALUES LESS THAN MAXVALUE</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.09 sec)
</pre><p>
You can see the partitions created by these two
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements using
the following query against the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table in the
<code class="literal">INFORMATION_SCHEMA</code> database:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH</code></strong>
> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.PARTITIONS</code></strong>
> <strong class="userinput"><code>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';</code></strong>
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 0 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 0 | 0 | 0 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)
</pre><p>
(For more information about this table, see
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table">Section 20.19, “The <code class="literal">INFORMATION_SCHEMA PARTITIONS</code> Table”</a>.) Now let us populate each
of these tables with a single row containing a
<code class="literal">NULL</code> in the column used as the partitioning
key, and verify that the rows were inserted using a pair of
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO t1 VALUES (NULL, 'mothra');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO t2 VALUES (NULL, 'mothra');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM t1;</code></strong>
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM t2;</code></strong>
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
</pre><p>
You can see which partitions are used to store the inserted
rows by rerunning the previous query against
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">INFORMATION_SCHEMA.PARTITIONS</code></a> and
inspecting the output:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH</code></strong>
> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.PARTITIONS</code></strong>
> <strong class="userinput"><code>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';</code></strong>
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
<span class="emphasis"><em>| t1 | p0 | 1 | 20 | 20 |</em></span>
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
<span class="emphasis"><em>| t2 | p0 | 1 | 20 | 20 |</em></span>
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
</pre><p>
You can also demonstrate that these rows were stored in the
lowest partition of each table by dropping these partitions,
and then re-running the <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>
statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE t1 DROP PARTITION p0;</code></strong>
Query OK, 0 rows affected (0.16 sec)
mysql> <strong class="userinput"><code>ALTER TABLE t2 DROP PARTITION p0;</code></strong>
Query OK, 0 rows affected (0.16 sec)
mysql> <strong class="userinput"><code>SELECT * FROM t1;</code></strong>
Empty set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM t2;</code></strong>
Empty set (0.00 sec)
</pre><p>
(For more information on <code class="literal">ALTER TABLE ... DROP
PARTITION</code>, see <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax">Section 12.1.7, “<code class="literal">ALTER TABLE</code> Syntax”</a>.)
</p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
Prior to MySQL 5.1.8, <code class="literal">RANGE</code> partitioning
treated a partitioning expression value of
<code class="literal">NULL</code> as 0 with respect to determining
placement. (The only way to circumvent this behavior was to
design tables so as not to allow nulls, usually by declaring
columns <code class="literal">NOT NULL</code>.) If you have a
<code class="literal">RANGE</code> partitioning scheme that depends on
this earlier behavior, you must re-implement it when upgrading
to MySQL 5.1.8 or later. (<a href="http://bugs.mysql.com/15447" target="_top">Bug#15447</a>)
</p></div><p>
<code class="literal">NULL</code> is also treated in this way for
partitioning expressions that use SQL functions. Suppose that we
define a table using a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement such as this one:
</p><pre class="programlisting">CREATE TABLE tndate (
id INT,
dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
</pre><p>
As with other MySQL functions,
<a href="functions.html#function_year"><code class="literal">YEAR(NULL)</code></a> returns
<code class="literal">NULL</code>. A row with a <code class="literal">dt</code>
column value of <code class="literal">NULL</code> is treated as though the
partitioning expression evaluated to a value less than any other
value, and so is inserted into partition <code class="literal">p0</code>.
</p><p><b>Handling of <code class="literal">NULL</code> with <code class="literal">LIST</code>
partitioning. </b>
A table that is partitioned by <code class="literal">LIST</code> admits
<code class="literal">NULL</code> values if and only if one of its
partitions is defined using that value-list that contains
<code class="literal">NULL</code>. The converse of this is that a table
partitioned by <code class="literal">LIST</code> which does not
explicitly use <code class="literal">NULL</code> in a value list rejects
rows resulting in a <code class="literal">NULL</code> value for the
partitioning expression, as shown in this example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE ts1 (</code></strong>
-> <strong class="userinput"><code>c1 INT,</code></strong>
-> <strong class="userinput"><code>c2 VARCHAR(20)</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY LIST(c1) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES IN (0, 3, 6),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES IN (1, 4, 7),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES IN (2, 5, 8)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>INSERT INTO ts1 VALUES (9, 'mothra');</code></strong>
<span class="errortext">ERROR 1504 (HY000): Table has no partition for value 9</span>
mysql> <strong class="userinput"><code>INSERT INTO ts1 VALUES (NULL, 'mothra');</code></strong>
<span class="errortext">ERROR 1504 (HY000): Table has no partition for value NULL</span>
</pre><p>
Only rows having a <code class="literal">c1</code> value between
<code class="literal">0</code> and <code class="literal">8</code> inclusive can be
inserted into <code class="literal">ts1</code>. <code class="literal">NULL</code>
falls outside this range, just like the number
<code class="literal">9</code>. We can create tables
<code class="literal">ts2</code> and <code class="literal">ts3</code> having value
lists containing <code class="literal">NULL</code>, as shown here:
</p><pre class="programlisting">mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
</pre><p>
When defining value lists for partitioning, you can (and
should) treat <code class="literal">NULL</code> just as you would any
other value. For example, both <code class="literal">VALUES IN
(NULL)</code> and <code class="literal">VALUES IN (1, 4, 7,
NULL)</code> are valid, as are <code class="literal">VALUES IN (1, NULL,
4, 7)</code>, <code class="literal">VALUES IN (NULL, 1, 4, 7)</code>,
and so on. You can insert a row having <code class="literal">NULL</code>
for column <code class="literal">c1</code> into each of the tables
<code class="literal">ts2</code> and <code class="literal">ts3</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO ts2 VALUES (NULL, 'mothra');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO ts3 VALUES (NULL, 'mothra');</code></strong>
Query OK, 1 row affected (0.00 sec)
</pre><p>
By issuing the appropriate query against
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">INFORMATION_SCHEMA.PARTITIONS</code></a>,
you can determine which partitions were used to store the rows
just inserted (we assume, as in the previous examples, that
the partitioned tables were created in the
<code class="literal">p</code> database):
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH</code></strong>
> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.PARTITIONS</code></strong>
> <strong class="userinput"><code>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';</code></strong>
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2 | p0 | 0 | 0 | 0 |
| ts2 | p1 | 0 | 0 | 0 |
| ts2 | p2 | 0 | 0 | 0 |
<span class="emphasis"><em>| ts2 | p3 | 1 | 20 | 20 |</em></span>
| ts3 | p0 | 0 | 0 | 0 |
<span class="emphasis"><em>| ts3 | p1 | 1 | 20 | 20 |</em></span>
| ts3 | p2 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)
</pre><p>
As shown earlier in this section, you can also verify which
partitions were used for storing the rows by deleting these
partitions and then performing a
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>.
</p><p><b>Handling of <code class="literal">NULL</code> with <code class="literal">HASH</code> and
<code class="literal">KEY</code> partitioning. </b>
<code class="literal">NULL</code> is handled somewhat differently for
tables partitioned by <code class="literal">HASH</code> or
<code class="literal">KEY</code>. In these cases, any partition
expression that yields a <code class="literal">NULL</code> value is
treated as though its return value were zero. We can verify
this behavior by examining the effects on the file system of
creating a table partitioned by <code class="literal">HASH</code> and
populating it with a record containing appropriate values.
Suppose that you have a table <code class="literal">th</code> (also in
the <code class="literal">p</code> database) created using the following
statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE th (</code></strong>
-> <strong class="userinput"><code>c1 INT,</code></strong>
-> <strong class="userinput"><code>c2 VARCHAR(20)</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY HASH(c1)</code></strong>
-> <strong class="userinput"><code>PARTITIONS 2;</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
The partitions belonging to this table can be viewed like
this:
</p><pre class="programlisting">mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 0 |
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
</pre><p>
Note that TABLE_ROWS for each partition is 0. Now insert two
rows into <code class="literal">th</code> whose <code class="literal">c1</code>
column values are <code class="literal">NULL</code> and 0, and verify
that these rows were inserted:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM th;</code></strong>
+------+---------+
| c1 | c2 |
+------+---------+
| NULL | mothra |
+------+---------+
| 0 | gigan |
+------+---------+
2 rows in set (0.01 sec)
</pre><p>
Recall that for any integer <em class="replaceable"><code>N</code></em>, the
value of <code class="literal">NULL MOD
<em class="replaceable"><code>N</code></em></code> is always
<code class="literal">NULL</code>. For tables that are partitioned by
<code class="literal">HASH</code> or <code class="literal">KEY</code>, this result
is treated for determining the correct partition as
<code class="literal">0</code>. Checking the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">INFORMATION_SCHEMA.PARTITIONS</code></a>
table once again, we can see that both rows were inserted into
partition <code class="literal">p0</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH</code></strong>
> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.PARTITIONS</code></strong>
> <strong class="userinput"><code>WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';</code></strong>
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
<span class="emphasis"><em>| th | p0 | 2 | 20 | 20 |</em></span>
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
</pre><p>
If you repeat this example using <code class="literal">PARTITION BY
KEY</code> in place of <code class="literal">PARTITION BY HASH</code>
in the definition of the table, you can verify easily that
<code class="literal">NULL</code> is also treated like 0 for this type
of partitioning as well.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="partitioning-management"></a>18.3. Partition Management</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="partitioning.html#partitioning-management-range-list">18.3.1. Management of <code class="literal">RANGE</code> and <code class="literal">LIST</code>
Partitions</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-management-hash-key">18.3.2. Management of <code class="literal">HASH</code> and <code class="literal">KEY</code>
Partitions</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-maintenance">18.3.3. Maintenance of Partitions</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-info">18.3.4. Obtaining Information About Partitions</a></span></dt></dl></div><a class="indexterm" name="id4824465"></a><a class="indexterm" name="id4824474"></a><a class="indexterm" name="id4824486"></a><a class="indexterm" name="id4824498"></a><a class="indexterm" name="id4824511"></a><p>
MySQL 5.1 provides a number of ways to modify
partitioned tables. It is possible to add, drop, redefine, merge,
or split existing partitions. All of these actions can be carried
out using the partitioning extensions to the
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> command (see
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax">Section 12.1.7, “<code class="literal">ALTER TABLE</code> Syntax”</a>, for syntax definitions). There are
also ways to obtain information about partitioned tables and
partitions. We discuss these topics in the sections that follow.
</p><div class="itemizedlist"><ul type="disc"><li><p>
For information about partition management in tables
partitioned by <code class="literal">RANGE</code> or
<code class="literal">LIST</code>, see
<a href="partitioning.html#partitioning-management-range-list" title="18.3.1. Management of RANGE and LIST
Partitions">Section 18.3.1, “Management of <code class="literal">RANGE</code> and <code class="literal">LIST</code>
Partitions”</a>.
</p></li><li><p>
For a discussion of managing <code class="literal">HASH</code> and
<code class="literal">KEY</code> partitions, see
<a href="partitioning.html#partitioning-management-hash-key" title="18.3.2. Management of HASH and KEY
Partitions">Section 18.3.2, “Management of <code class="literal">HASH</code> and <code class="literal">KEY</code>
Partitions”</a>.
</p></li><li><p>
See <a href="partitioning.html#partitioning-info" title="18.3.4. Obtaining Information About Partitions">Section 18.3.4, “Obtaining Information About Partitions”</a>, for a discussion of
mechanisms provided in MySQL 5.1 for obtaining
information about partitioned tables and partitions.
</p></li><li><p>
For a discussion of performing maintenance operations on
partitions, see <a href="partitioning.html#partitioning-maintenance" title="18.3.3. Maintenance of Partitions">Section 18.3.3, “Maintenance of Partitions”</a>.
</p></li></ul></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In MySQL 5.1, all partitions of a partitioned table
must have the same number of subpartitions, and it is not
possible to change the subpartitioning once the table has been
created.
</p></div><p>
The statement <code class="literal">ALTER TABLE ... PARTITION BY ...</code>
is available and is functional beginning with MySQL 5.1.6;
previously in MySQL 5.1, this was accepted as valid syntax, but
the statement did nothing.
</p><p>
To change a table's partitioning scheme, it is necessary only to
use the <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> command with a
<em class="replaceable"><code>partition_options</code></em> clause. This clause
has the same syntax as that as used with
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> for creating a
partitioned table, and always begins with the keywords
<code class="literal">PARTITION BY</code>. For example, suppose that you
have a table partitioned by range using the following
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement:
</p><pre class="programlisting">CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
</pre><p>
To repartition this table so that it is partitioned by key into
two partitions using the <code class="literal">id</code> column value as the
basis for the key, you can use this statement:
</p><pre class="programlisting">ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
</pre><p>
This has the same effect on the structure of the table as dropping
the table and re-creating it using <code class="literal">CREATE TABLE trb3
PARTITION BY KEY(id) PARTITIONS 2;</code>.
</p><p>
In MySQL 5.1.7 and earlier MySQL 5.1 releases, <code class="literal">ALTER
TABLE ... ENGINE = ...</code> removed all partitioning from the
affected table. Beginning with MySQL 5.1.8, this statement changes
only the storage engine used by the table, and leaves the
table's partitioning scheme intact. As of MySQL 5.1.8, use
<code class="literal">ALTER TABLE ... REMOVE PARTITIONING</code> to remove a
table's partitioning. See <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax">Section 12.1.7, “<code class="literal">ALTER TABLE</code> Syntax”</a>.
</p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
Only a single <code class="literal">PARTITION BY</code>, <code class="literal">ADD
PARTITION</code>, <code class="literal">DROP PARTITION</code>,
<code class="literal">REORGANIZE PARTITION</code>, or <code class="literal">COALESCE
PARTITION</code> clause can be used in a given
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statement. If you
(for example) wish to drop a partition and reorganize a
table's remaining partitions, you must do so in two
separate <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statements
(one using <code class="literal">DROP PARTITION</code> and then a second
one using <code class="literal">REORGANIZE PARITITIONS</code>).
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-management-range-list"></a>18.3.1. Management of <code class="literal">RANGE</code> and <code class="literal">LIST</code>
Partitions</h3></div></div></div><a class="indexterm" name="id4824815"></a><a class="indexterm" name="id4824828"></a><a class="indexterm" name="id4824840"></a><a class="indexterm" name="id4824852"></a><p>
Range and list partitions are very similar with regard to how
the adding and dropping of partitions are handled. For this
reason we discuss the management of both sorts of partitioning
in this section. For information about working with tables that
are partitioned by hash or key, see
<a href="partitioning.html#partitioning-management-hash-key" title="18.3.2. Management of HASH and KEY
Partitions">Section 18.3.2, “Management of <code class="literal">HASH</code> and <code class="literal">KEY</code>
Partitions”</a>. Dropping a
<code class="literal">RANGE</code> or <code class="literal">LIST</code> partition is
more straightforward than adding one, so we discuss this first.
</p><p>
Dropping a partition from a table that is partitioned by either
<code class="literal">RANGE</code> or by <code class="literal">LIST</code> can be
accomplished using the <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER
TABLE</code></a> statement with a <code class="literal">DROP
PARTITION</code> clause. Here is a very basic example, which
supposes that you have already created a table which is
partitioned by range and then populated with 10 records using
the following <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> and
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)</code></strong>
-> <strong class="userinput"><code>PARTITION BY RANGE( YEAR(purchased) ) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES LESS THAN (1990),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES LESS THAN (1995),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES LESS THAN (2000),</code></strong>
-> <strong class="userinput"><code>PARTITION p3 VALUES LESS THAN (2005)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>INSERT INTO tr VALUES</code></strong>
-> <strong class="userinput"><code>(1, 'desk organiser', '2003-10-15'),</code></strong>
-> <strong class="userinput"><code>(2, 'CD player', '1993-11-05'),</code></strong>
-> <strong class="userinput"><code>(3, 'TV set', '1996-03-10'),</code></strong>
-> <strong class="userinput"><code>(4, 'bookcase', '1982-01-10'),</code></strong>
-> <strong class="userinput"><code>(5, 'exercise bike', '2004-05-09'),</code></strong>
-> <strong class="userinput"><code>(6, 'sofa', '1987-06-05'),</code></strong>
-> <strong class="userinput"><code>(7, 'popcorn maker', '2001-11-22'),</code></strong>
-> <strong class="userinput"><code>(8, 'aquarium', '1992-08-04'),</code></strong>
-> <strong class="userinput"><code>(9, 'study desk', '1984-09-16'),</code></strong>
-> <strong class="userinput"><code>(10, 'lava lamp', '1998-12-25');</code></strong>
Query OK, 10 rows affected (0.01 sec)
</pre><p>
You can see which items should have been inserted into partition
<code class="literal">p2</code> as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM tr</code></strong>
-> <strong class="userinput"><code>WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';</code></strong>
+------+-----------+------------+
| id | name | purchased |
+------+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)
</pre><p>
To drop the partition named <code class="literal">p2</code>, execute the
following command:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE tr DROP PARTITION p2;</code></strong>
Query OK, 0 rows affected (0.03 sec)
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The <code class="literal">NDBCLUSTER</code> storage engine
does not support <code class="literal">ALTER TABLE ... DROP
PARTITION</code>. It does, however, support the other
partitioning-related extensions to <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER
TABLE</code></a> that are described in this chapter.
</p></div><p>
It is very important to remember that, <span class="emphasis"><em>when you drop a
partition, you also delete all the data that was stored in that
partition</em></span>. You can see that this is the case by
re-running the previous <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>
query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM tr WHERE purchased</code></strong>
-> <strong class="userinput"><code>BETWEEN '1995-01-01' AND '1999-12-31';</code></strong>
Empty set (0.00 sec)
</pre><p>
Because of this, the requirement was added in MySQL 5.1.10 that
you have the <a href="server-administration.html#priv_drop"><code class="literal">DROP</code></a> privilege for a
table before you can execute <code class="literal">ALTER TABLE ... DROP
PARTITION</code> on that table.
</p><p>
If you wish to drop all data from all partitions while
preserving the table definition and its partitioning scheme, use
the <a href="sql-syntax.html#truncate" title="12.2.10. TRUNCATE Syntax"><code class="literal">TRUNCATE
TABLE</code></a> command. (See <a href="sql-syntax.html#truncate" title="12.2.10. TRUNCATE Syntax">Section 12.2.10, “<code class="literal">TRUNCATE</code> Syntax”</a>.)
</p><p>
If you intend to change the partitioning of a table
<span class="emphasis"><em>without</em></span> losing data, use <code class="literal">ALTER
TABLE ... REORGANIZE PARTITION</code> instead. See below or
in <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax">Section 12.1.7, “<code class="literal">ALTER TABLE</code> Syntax”</a>, for information about
<code class="literal">REORGANIZE PARTITION</code>.
</p><p>
If you now execute a <a href="sql-syntax.html#show-create-table" title="12.5.5.12. SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE
TABLE</code></a> command, you can see how the partitioning makeup
of the table has been changed:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CREATE TABLE tr\G</code></strong>
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)
</pre><p>
When you insert new rows into the changed table with
<code class="literal">purchased</code> column values between
<code class="literal">'1995-01-01'</code> and
<code class="literal">'2004-12-31'</code> inclusive, those rows will be
stored in partition <code class="literal">p3</code>. You can verify this
as follows:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM tr WHERE purchased</code></strong>
-> <strong class="userinput"><code>BETWEEN '1995-01-01' AND '2004-12-31';</code></strong>
+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 11 | pencil holder | 1995-07-12 |
| 1 | desk organiser | 2003-10-15 |
| 5 | exercise bike | 2004-05-09 |
| 7 | popcorn maker | 2001-11-22 |
+------+----------------+------------+
4 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>ALTER TABLE tr DROP PARTITION p3;</code></strong>
Query OK, 0 rows affected (0.03 sec)
mysql> <strong class="userinput"><code>SELECT * FROM tr WHERE purchased</code></strong>
-> <strong class="userinput"><code>BETWEEN '1995-01-01' AND '2004-12-31';</code></strong>
Empty set (0.00 sec)
</pre><p>
Note that the number of rows dropped from the table as a result
of <code class="literal">ALTER TABLE ... DROP PARTITION</code> is not
reported by the server as it would be by the equivalent
<a href="sql-syntax.html#delete" title="12.2.2. DELETE Syntax"><code class="literal">DELETE</code></a> query.
</p><p>
Dropping <code class="literal">LIST</code> partitions uses exactly the
same <code class="literal">ALTER TABLE ... DROP PARTITION</code> syntax as
used for dropping <code class="literal">RANGE</code> partitions. However,
there is one important difference in the effect this has on your
use of the table afterward: You can no longer insert into the
table any rows having any of the values that were included in
the value list defining the deleted partition. (See
<a href="partitioning.html#partitioning-list" title="18.2.2. LIST Partitioning">Section 18.2.2, “<code class="literal">LIST</code> Partitioning”</a>, for an example.)
</p><p>
To add a new range or list partition to a previously partitioned
table, use the <code class="literal">ALTER TABLE ... ADD PARTITION</code>
statement. For tables which are partitioned by
<code class="literal">RANGE</code>, this can be used to add a new range to
the end of the list of existing partitions. For example, suppose
that you have a partitioned table containing membership data for
your organisation, which is defined as follows:
</p><pre class="programlisting">CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1970),
PARTITION p1 VALUES LESS THAN (1980),
PARTITION p2 VALUES LESS THAN (1990)
);
</pre><p>
Suppose further that the minimum age for members is 16. As the
calendar approaches the end of 2005, you realize that you will
soon be admitting members who were born in 1990 (and later in
years to come). You can modify the <code class="literal">members</code>
table to accommodate new members born in the years
1990–1999 as shown here:
</p><pre class="programlisting">ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
</pre><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
With tables that are partitioned by range, you can use
<code class="literal">ADD PARTITION</code> to add new partitions to the
high end of the partitions list only. Trying to add a new
partition in this manner between or before existing partitions
will result in an error as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE members</code></strong>
> <strong class="userinput"><code>ADD PARTITION (</code></strong>
> <strong class="userinput"><code>PARTITION p3 VALUES LESS THAN (1960));</code></strong>
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
increasing for each partition
</pre><p>
</p></div><p>
In a similar fashion, you can add new partitions to a table that
is partitioned by <code class="literal">LIST</code>. For example, given a
table defined like so:
</p><pre class="programlisting">CREATE TABLE tt (
id INT,
data INT
)
PARTITION BY LIST(data) (
PARTITION p0 VALUES IN (5, 10, 15),
PARTITION p1 VALUES IN (6, 12, 18)
);
</pre><p>
You can add a new partition in which to store rows having the
<code class="literal">data</code> column values <code class="literal">7</code>,
<code class="literal">14</code>, and <code class="literal">21</code> as shown:
</p><pre class="programlisting">ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
</pre><p>
Note that you <span class="emphasis"><em>cannot</em></span> add a new
<code class="literal">LIST</code> partition encompassing any values that
are already included in the value list of an existing partition.
If you attempt to do so, an error will result:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE tt ADD PARTITION </code></strong>
> <strong class="userinput"><code>(PARTITION np VALUES IN (4, 8, 12));</code></strong>
ERROR 1465 (HY000): Multiple definition of same constant »
in list partitioning
</pre><p>
Because any rows with the <code class="literal">data</code> column value
<code class="literal">12</code> have already been assigned to partition
<code class="literal">p1</code>, you cannot create a new partition on
table <code class="literal">tt</code> that includes <code class="literal">12</code>
in its value list. To accomplish this, you could drop
<code class="literal">p1</code>, and add <code class="literal">np</code> and then a
new <code class="literal">p1</code> with a modified definition. However,
as discussed earlier, this would result in the loss of all data
stored in <code class="literal">p1</code> — and it is often the case
that this is not what you really want to do. Another solution
might appear to be to make a copy of the table with the new
partitioning and to copy the data into it using
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE ...
SELECT ...</code></a>, then drop the old table and rename the new
one, but this could be very time-consuming when dealing with a
large amounts of data. This also might not be feasible in
situations where high availability is a requirement.
</p><p>
Beginning with MySQL 5.1.6, you can add multiple partitions in a
single <code class="literal">ALTER TABLE ... ADD PARTITION</code>
statement as shown here:
</p><pre class="programlisting">CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (1996),
PARTITION p3 VALUES LESS THAN (2001),
PARTITION p4 VALUES LESS THAN (2005)
);
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
</pre><p>
Fortunately, MySQL's partitioning implementation provides ways
to redefine partitions without losing data. Let us look first at
a couple of simple examples involving <code class="literal">RANGE</code>
partitioning. Recall the <code class="literal">members</code> table which
is now defined as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CREATE TABLE members\G</code></strong>
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) default NULL,
`fname` varchar(25) default NULL,
`lname` varchar(25) default NULL,
`dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)
</pre><p>
Suppose that you would like to move all rows representing
members born before 1960 into a separate partition. As we have
already seen, this cannot be done using <code class="literal">ALTER TABLE ...
ADD PARTITION</code>. However, you can use another
partition-related extension to <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER
TABLE</code></a> in order to accomplish this:
</p><pre class="programlisting">ALTER TABLE members REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);
</pre><p>
In effect, this command splits partition <code class="literal">p0</code>
into two new partitions <code class="literal">s0</code> and
<code class="literal">s1</code>. It also moves the data that was stored in
<code class="literal">p0</code> into the new partitions according to the
rules embodied in the two <code class="literal">PARTITION ... VALUES
...</code> clauses, so that <code class="literal">s0</code> contains
only those records for which
<a href="functions.html#function_year"><code class="literal">YEAR(dob)</code></a> is less than 1960 and
<code class="literal">s1</code> contains those rows in which
<a href="functions.html#function_year"><code class="literal">YEAR(dob)</code></a> is greater than or
equal to 1960 but less than 1970.
</p><p>
A <code class="literal">REORGANIZE PARTITION</code> clause may also be
used for merging adjacent partitions. You can return the
<code class="literal">members</code> table to its previous partitioning as
shown here:
</p><pre class="programlisting">ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
);
</pre><p>
No data is lost in splitting or merging partitions using
<code class="literal">REORGANIZE PARTITION</code>. In executing the above
statement, MySQL moves all of the records that were stored in
partitions <code class="literal">s0</code> and <code class="literal">s1</code> into
partition <code class="literal">p0</code>.
</p><p>
The general syntax for <code class="literal">REORGANIZE PARTITION</code>
is:
</p><pre class="programlisting">ALTER TABLE <em class="replaceable"><code>tbl_name</code></em>
REORGANIZE PARTITION <em class="replaceable"><code>partition_list</code></em>
INTO (<em class="replaceable"><code>partition_definitions</code></em>);
</pre><p>
Here, <em class="replaceable"><code>tbl_name</code></em> is the name of the
partitioned table, and <em class="replaceable"><code>partition_list</code></em>
is a comma-separated list of names of one or more existing
partitions to be changed.
<em class="replaceable"><code>partition_definitions</code></em> is a
comma-separated list of new partition definitions, which follow
the same rules as for the
<em class="replaceable"><code>partition_definitions</code></em> list used in
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> (see
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax">Section 12.1.17, “<code class="literal">CREATE TABLE</code> Syntax”</a>). It should be noted that you are
not limited to merging several partitions into one, or to
splitting one partition into many, when using
<code class="literal">REORGANIZE PARTITION</code>. For example, you can
reorganize all four partitions of the <code class="literal">members</code>
table into two, as follows:
</p><pre class="programlisting">ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
</pre><p>
You can also use <code class="literal">REORGANIZE PARTITION</code> with
tables that are partitioned by <code class="literal">LIST</code>. Let us
return to the problem of adding a new partition to the
list-partitioned <code class="literal">tt</code> table and failing because
the new partition had a value that was already present in the
value-list of one of the existing partitions. We can handle this
by adding a partition that contains only nonconflicting values,
and then reorganizing the new partition and the existing one so
that the value which was stored in the existing one is now moved
to the new one:
</p><pre class="programlisting">ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
</pre><p>
Here are some key points to keep in mind when using
<code class="literal">ALTER TABLE ... REORGANIZE PARTITION</code> to
repartition tables that are partitioned by
<code class="literal">RANGE</code> or <code class="literal">LIST</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">PARTITION</code> clauses used to determine
the new partitioning scheme are subject to the same rules as
those used with a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement.
</p><p>
Most importantly, you should remember that the new
partitioning scheme cannot have any overlapping ranges
(applies to tables partitioned by <code class="literal">RANGE</code>)
or sets of values (when reorganizing tables partitioned by
<code class="literal">LIST</code>).
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Prior to MySQL 5.1.4, you could not reuse the names of
existing partitions in the <code class="literal">INTO</code> clause,
even when those partitions were being dropped or
redefined. See <a href="news.html#news-5-1-4" title="C.1.41. Changes in MySQL 5.1.4 (21 December 2005)">Section C.1.41, “Changes in MySQL 5.1.4 (21 December 2005)”</a>, for more
information.
</p></div></li><li><p>
The combination of partitions in the
<em class="replaceable"><code>partition_definitions</code></em> list should
account for the same range or set of values overall as the
combined partitions named in the
<em class="replaceable"><code>partition_list</code></em>.
</p><p>
For instance, in the <code class="literal">members</code> table used
as an example in this section, partitions
<code class="literal">p1</code> and <code class="literal">p2</code> together
cover the years 1980 through 1999. Therefore, any
reorganization of these two partitions should cover the same
range of years overall.
</p></li><li><p>
For tables partitioned by <code class="literal">RANGE</code>, you can
reorganize only adjacent partitions; you cannot skip over
range partitions.
</p><p>
For instance, you could not reorganize the
<code class="literal">members</code> table used as an example in this
section using a statement beginning with <code class="literal">ALTER
TABLE members REORGANIZE PARTITION p0,p2 INTO ...</code>
because <code class="literal">p0</code> covers the years prior to 1970
and <code class="literal">p2</code> the years from 1990 through 1999
inclusive, and thus the two are not adjacent partitions.
</p></li><li><p>
You cannot use <code class="literal">REORGANIZE PARTITION</code> to
change the table's partitioning type; that is, you cannot
(for example) change <code class="literal">RANGE</code> partitions to
<code class="literal">HASH</code> partitions or <span class="foreignphrase"><em class="foreignphrase">vice
versa</em></span>. You also cannot use this command to
change the partitioning expression or column. To accomplish
either of these tasks without dropping and re-creating the
table, you can use <code class="literal">ALTER TABLE ... PARTITION BY
...</code>. For example:
</p><pre class="programlisting">ALTER TABLE members
PARTITION BY HASH( YEAR(dob) )
PARTITIONS 8;
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-management-hash-key"></a>18.3.2. Management of <code class="literal">HASH</code> and <code class="literal">KEY</code>
Partitions</h3></div></div></div><a class="indexterm" name="id4826149"></a><a class="indexterm" name="id4826161"></a><a class="indexterm" name="id4826173"></a><a class="indexterm" name="id4826186"></a><p>
Tables which are partitioned by hash or by key are very similar
to one another with regard to making changes in a partitioning
setup, and both differ in a number of ways from tables which
have been partitioned by range or list. For that reason, this
section addresses the modification of tables partitioned by hash
or by key only. For a discussion of adding and dropping of
partitions of tables that are partitioned by range or list, see
<a href="partitioning.html#partitioning-management-range-list" title="18.3.1. Management of RANGE and LIST
Partitions">Section 18.3.1, “Management of <code class="literal">RANGE</code> and <code class="literal">LIST</code>
Partitions”</a>.
</p><p>
You cannot drop partitions from tables that are partitioned by
<code class="literal">HASH</code> or <code class="literal">KEY</code> in the same
way that you can from tables that are partitioned by
<code class="literal">RANGE</code> or <code class="literal">LIST</code>. However,
you can merge <code class="literal">HASH</code> or <code class="literal">KEY</code>
partitions using the <code class="literal">ALTER TABLE ... COALESCE
PARTITION</code> command. For example, suppose that you have
a table containing data about clients, which is divided into
twelve partitions. The <code class="literal">clients</code> table is
defined as shown here:
</p><pre class="programlisting">CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
</pre><p>
To reduce the number of partitions from twelve to eight, execute
the following <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
command:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE clients COALESCE PARTITION 4;</code></strong>
Query OK, 0 rows affected (0.02 sec)
</pre><p>
<code class="literal">COALESCE</code> works equally well with tables that
are partitioned by <code class="literal">HASH</code>,
<code class="literal">KEY</code>, <code class="literal">LINEAR HASH</code>, or
<code class="literal">LINEAR KEY</code>. Here is an example similar to the
previous one, differing only in that the table is partitioned by
<code class="literal">LINEAR KEY</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE clients_lk (</code></strong>
-> <strong class="userinput"><code>id INT,</code></strong>
-> <strong class="userinput"><code>fname VARCHAR(30),</code></strong>
-> <strong class="userinput"><code>lname VARCHAR(30),</code></strong>
-> <strong class="userinput"><code>signed DATE</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY LINEAR KEY(signed)</code></strong>
-> <strong class="userinput"><code>PARTITIONS 12;</code></strong>
Query OK, 0 rows affected (0.03 sec)
mysql> <strong class="userinput"><code>ALTER TABLE clients_lk COALESCE PARTITION 4;</code></strong>
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
</pre><p>
Note that the number following <code class="literal">COALESCE
PARTITION</code> is the number of partitions to merge into
the remainder — in other words, it is the number of
partitions to remove from the table.
</p><p>
If you attempt to remove more partitions than the table has, the
result is an error like the one shown:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE clients COALESCE PARTITION 18;</code></strong>
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead
</pre><p>
To increase the number of partitions for the
<code class="literal">clients</code> table from 12 to 18. use
<code class="literal">ALTER TABLE ... ADD PARTITION</code> as shown here:
</p><pre class="programlisting">ALTER TABLE clients ADD PARTITION PARTITIONS 6;
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-maintenance"></a>18.3.3. Maintenance of Partitions</h3></div></div></div><a class="indexterm" name="id4826456"></a><a class="indexterm" name="id4826469"></a><a class="indexterm" name="id4826481"></a><a class="indexterm" name="id4826494"></a><a class="indexterm" name="id4826506"></a><a class="indexterm" name="id4826518"></a><a class="indexterm" name="id4826531"></a><a class="indexterm" name="id4826543"></a><p>
A number of table and partition maintenance tasks can be carried
out using SQL statements intended for such purposes on
partitioned tables in MySQL 5.1.
</p><p>
Table maintenance of partitioned tables can be accomplished
using the statements <a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a>,
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a>,
<a href="sql-syntax.html#analyze-table" title="12.5.2.1. ANALYZE TABLE Syntax"><code class="literal">ANALYZE TABLE</code></a>, and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>, which are supported
for partitioned tables as of MySQL 5.1.27.
</p><p>
Also beginning with MySQL 5.1.27, you can use a number of
extensions to <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> for
performing operations of this type on one or more partitions
directly, as described in the following list:
</p><div class="itemizedlist"><ul type="disc"><li><p><b>Rebuilding partitions. </b>
Rebuilds the partition; this has the same effect as
dropping all records stored in the partition, then
reinserting them. This can be useful for purposes of
defragmentation.
</p><p>
Example:
</p><pre class="programlisting">ALTER TABLE t1 REBUILD PARTITION p0, p1;
</pre></li><li><p><b>Optimizing partitions. </b>
If you have deleted a large number of rows from a
partition or if you have made many changes to a
partitioned table with variable-length rows (that is,
having <code class="literal">VARCHAR</code>,
<code class="literal">BLOB</code>, or <code class="literal">TEXT</code>
columns), you can use <code class="literal">ALTER TABLE ... OPTIMIZE
PARTITION</code> to reclaim any unused space and to
defragment the partition data file.
</p><p>
Example:
</p><pre class="programlisting">ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
</pre><p>
Using <code class="literal">OPTIMIZE PARTITION</code> on a given
partition is equivalent to running <code class="literal">CHECK
PARTITION</code>, <code class="literal">ANALYZE PARTITION</code>,
and <code class="literal">REPAIR PARTITION</code> on that partition.
</p></li><li><p><b>Analyzing partitions. </b>
This reads and stores the key distributions for
partitions.
</p><p>
Example:
</p><pre class="programlisting">ALTER TABLE t1 ANALYZE PARTITION p3;</pre></li><li><p><b>Repairing partitions. </b>
This repairs corrupted partitions.
</p><p>
Example:
</p><pre class="programlisting">ALTER TABLE t1 REPAIR PARTITION p0,p1;
</pre></li><li><p><b>Checking partitions. </b>
You can check partitions for errors in much the same way
that you can use <code class="literal">CHECK TABLE</code> with
nonpartitioned tables.
</p><p>
Example:
</p><pre class="programlisting">ALTER TABLE trb3 CHECK PARTITION p1;
</pre><p>
This command will tell you if the data or indexes in
partition <code class="literal">p1</code> of table
<code class="literal">t1</code> are corrupted. If this is the case,
use <code class="literal">ALTER TABLE ... REPAIR PARTITION</code> to
repair the partition.
</p></li></ul></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The statements <code class="literal">ALTER TABLE ... ANALYZE
PARTITION</code>, <code class="literal">ALTER TABLE ... CHECK
PARTITION</code>, <code class="literal">ALTER TABLE ... OPTIMIZE
PARTITION</code>, and <code class="literal">ALTER TABLE ... REPAIR
PARTITION</code> were originally introduced in MySQL 5.1.5,
but did not work properly and were disabled in MySQL 5.1.24.
They were re-introduced in MySQL 5.1.27. (<a href="http://bugs.mysql.com/20129" target="_top">Bug#20129</a>) The use
of these partitioning-specific <code class="literal">ALTER TABLE</code>
statements with tables which are not partitioned is not
supported; beginning with MySQL 5.1.31, it is expressly
disallowed. (<a href="http://bugs.mysql.com/39434" target="_top">Bug#39434</a>)
</p><p>
<code class="literal">ALTER TABLE ... REBUILD PARTITION</code> was also
introduced in MySQL 5.1.5.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-info"></a>18.3.4. Obtaining Information About Partitions</h3></div></div></div><a class="indexterm" name="id4826887"></a><a class="indexterm" name="id4826896"></a><a class="indexterm" name="id4826905"></a><a class="indexterm" name="id4826914"></a><p>
This section discusses obtaining information about existing
partitions, which can be done in a number of ways. These
include:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Using the <a href="sql-syntax.html#show-create-table" title="12.5.5.12. SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a>
statement to view the partitioning clauses used in creating
a partitioned table.
</p></li><li><p>
Using the <a href="sql-syntax.html#show-table-status" title="12.5.5.38. SHOW TABLE STATUS Syntax"><code class="literal">SHOW TABLE STATUS</code></a>
statement to determine whether a table is partitioned.
</p></li><li><p>
Querying the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">INFORMATION_SCHEMA.PARTITIONS</code></a>
table.
</p></li><li><p>
Using the statement <code class="literal">EXPLAIN PARTITIONS
SELECT</code> to see which partitions are used by a given
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>.
</p></li></ul></div><p>
As discussed elsewhere in this chapter,
<a href="sql-syntax.html#show-create-table" title="12.5.5.12. SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a> includes in its
output the <code class="literal">PARTITION BY</code> clause used to create
a partitioned table. For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CREATE TABLE trb3\G</code></strong>
*************************** 1. row ***************************
Table: trb3
Create Table: CREATE TABLE `trb3` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.00 sec)
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In early MySQL 5.1 releases, the <code class="literal">PARTITIONS</code>
clause was not shown for tables partitioned by
<code class="literal">HASH</code> or <code class="literal">KEY</code>. This issue
was fixed in MySQL 5.1.6.
</p></div><p>
<a href="sql-syntax.html#show-table-status" title="12.5.5.38. SHOW TABLE STATUS Syntax"><code class="literal">SHOW TABLE STATUS</code></a> works with
partitioned tables. Beginning with MySQL 5.1.9, its output is
the same as that for nonpartitioned tables, except that the
<code class="literal">Create_options</code> column contains the string
<code class="literal">partitioned</code>. In MySQL 5.1.8 and earlier, the
<code class="literal">Engine</code> column always contained the value
<code class="literal">PARTITION</code>; beginning with MySQL 5.1.9, this
column contains the name of the storage engine used by all
partitions of the table. (See
<a href="sql-syntax.html#show-table-status" title="12.5.5.38. SHOW TABLE STATUS Syntax">Section 12.5.5.38, “<code class="literal">SHOW TABLE STATUS</code> Syntax”</a>, for more information about
this statement.)
</p><p>
You can also obtain information about partitions from
<code class="literal">INFORMATION_SCHEMA</code>, which contains a
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table. See
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table">Section 20.19, “The <code class="literal">INFORMATION_SCHEMA PARTITIONS</code> Table”</a>.
</p><a class="indexterm" name="id4827118"></a><a class="indexterm" name="id4827130"></a><p>
Beginning with MySQL 5.1.5, it is possible to determine which
partitions of a partitioned table are involved in a given
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> query using
<a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN
PARTITIONS</code></a>. The <code class="literal">PARTITIONS</code> keyword
adds a <code class="literal">partitions</code> column to the output of
<a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN</code></a> listing the partitions
from which records would be matched by the query.
</p><p>
Suppose that you have a table <code class="literal">trb1</code> defined
and populated as follows:
</p><pre class="programlisting">CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (7),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (11)
);
INSERT INTO trb1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
</pre><p>
You can see which partitions are used in a query such as
<code class="literal">SELECT * FROM trb1;</code>, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>EXPLAIN PARTITIONS SELECT * FROM trb1\G</code></strong>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using filesort
</pre><p>
In this case, all four partitions are searched. However, when a
limiting condition making use of the partitioning key is added
to the query, you can see that only those partitions containing
matching values are scanned, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G</code></strong>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where
</pre><p>
<a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN
PARTITIONS</code></a> provides information about keys used and
possible keys, just as with the standard
<a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN
SELECT</code></a> statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE trb1 ADD PRIMARY KEY (id);</code></strong>
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> <strong class="userinput"><code>EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G</code></strong>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trb1
partitions: p0,p1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using where
</pre><p>
You should take note of the following restrictions and
limitations on <a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN
PARTITIONS</code></a>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
You cannot use the <code class="literal">PARTITIONS</code> and
<code class="literal">EXTENDED</code> keywords together in the same
<code class="literal">EXPLAIN ... SELECT</code> statement. Attempting
to do so produces a syntax error.
</p></li><li><p>
If <a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN
PARTITIONS</code></a> is used to examine a query against a
nonpartitioned table, no error is produced, but the value of
the <code class="literal">partitions</code> column is always
<code class="literal">NULL</code>.
</p></li></ul></div><p>
As of MySQL 5.1.28, the <code class="literal">rows</code> column of
<a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN
PARTITIONS</code></a> output always displays the total number of
records in the table. Previously, this was the number of
matching rows. (<a href="http://bugs.mysql.com/35745" target="_top">Bug#35745</a>)
</p><p>
See also <a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax">Section 12.3.2, “<code class="literal">EXPLAIN</code> Syntax”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="partitioning-pruning"></a>18.4. Partition Pruning</h2></div></div></div><a class="indexterm" name="id4827393"></a><a class="indexterm" name="id4827402"></a><p>
This section discusses an optimization known as
<em class="firstterm">partition pruning</em>, which was implemented
for partitioned tables in MySQL 5.1.6. The core concept behind
partition pruning is relatively simple, and can be described as
“<span class="quote">Do not scan partitions where there can be no matching
values</span>”. For example, suppose you have a partitioned table
<code class="literal">t1</code> defined by this statement:
</p><pre class="programlisting">CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
</pre><p>
Consider the case where you wish to obtain results from a query
such as this one:
</p><pre class="programlisting">SELECT fname, lname, region_code, dob
FROM t1
WHERE region_code > 125 AND region_code < 130;
</pre><p>
It is easy to see that none of the rows which ought to be returned
will be in either of the partitions <code class="literal">p0</code> or
<code class="literal">p3</code>; that is, we need to search only in
partitions <code class="literal">p1</code> and <code class="literal">p2</code> to find
matching rows. By doing so, it is possible to expend much more
time and effort in finding matching rows than it is to scan all
partitions in the table. This “<span class="quote">cutting away</span>” of
unneeded partitions is known as <em class="firstterm">pruning</em>.
When the optimizer can make use of partition pruning in performing
a query, execution of the query can be an order of magnitude
faster than the same query against a nonpartitioned table
containing the same column definitions and data.
</p><p>
The query optimizer can perform pruning whenever a
<code class="literal">WHERE</code> condition can be reduced to either one of
the following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal"><em class="replaceable"><code>partition_column</code></em> =
<em class="replaceable"><code>constant</code></em></code>
</p></li><li><p>
<code class="literal"><em class="replaceable"><code>partition_column</code></em> IN
(<em class="replaceable"><code>constant1</code></em>,
<em class="replaceable"><code>constant2</code></em>, ...,
<em class="replaceable"><code>constantN</code></em>)</code>
</p></li></ul></div><p>
In the first case, the optimizer simply evaluates the partitioning
expression for the value given, determines which partition
contains that value, and scans only this partition. In many cases,
the equals sign can be replaced with another arithmetic
comparison, including <code class="literal"><</code>,
<code class="literal">></code>, <code class="literal"><=</code>,
<code class="literal">>=</code>, and <code class="literal"><></code>. Some
queries using <code class="literal">BETWEEN</code> in the
<code class="literal">WHERE</code> clause can also take advantage of
partition pruning. See the examples later in this section.
</p><p>
In the second case, the optimizer evaluates the partitioning
expression for each value in the list, creates a list of matching
partitions, and then scans only the partitions in this partition
list.
</p><p>
Pruning can also be applied to short ranges, which the optimizer
can convert into equivalent lists of values. For instance, in the
previous example, the <code class="literal">WHERE</code> clause can be
converted to <code class="literal">WHERE region_code IN (125, 126, 127, 128,
129, 130)</code>. Then the optimizer can determine that the
first three values in the list are found in partition
<code class="literal">p1</code>, the remaining three values in partition
<code class="literal">p2</code>, and that the other partitions contain no
relevant values and so do not need to be searched for matching
rows.
</p><a class="indexterm" name="id4827632"></a><p>
This type of optimization can be applied whenever the partitioning
expression consists of an equality or a range which can be reduced
to a set of equalities, or when the partitioning expression
represents an increasing or decreasing relationship. Pruning can
also be applied for tables partitioned on a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> column when the
partitioning expression uses the
<a href="functions.html#function_year"><code class="literal">YEAR()</code></a> or
<a href="functions.html#function_to-days"><code class="literal">TO_DAYS()</code></a> function.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
We plan to add pruning support in a future MySQL release for
additional functions that act on a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> value, return an
integer, and are increasing or decreasing.
</p></div><p>
For example, suppose that table <code class="literal">t2</code>, defined as
shown here, is partitioned on a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> column:
</p><pre class="programlisting">CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION d0 VALUES LESS THAN (1970),
PARTITION d1 VALUES LESS THAN (1975),
PARTITION d2 VALUES LESS THAN (1980),
PARTITION d3 VALUES LESS THAN (1985),
PARTITION d4 VALUES LESS THAN (1990),
PARTITION d5 VALUES LESS THAN (2000),
PARTITION d6 VALUES LESS THAN (2005),
PARTITION d7 VALUES LESS THAN MAXVALUE
);
</pre><p>
The following queries on <code class="literal">t2</code> can make of use
partition pruning:
</p><pre class="programlisting">SELECT * FROM t2 WHERE dob = '1982-06-23';
SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'
</pre><p>
In the case of the last query, the optimizer can also act as
follows:
</p><div class="orderedlist"><ol type="1"><li><p>
<span class="emphasis"><em>Find the partition containing the low end of the
range</em></span>.
</p><p>
<a href="functions.html#function_year"><code class="literal">YEAR('1984-06-21')</code></a> yields the
value <code class="literal">1984</code>, which is found in partition
<code class="literal">d3</code>.
</p></li><li><p>
<span class="emphasis"><em>Find the partition containing the high end of the
range</em></span>.
</p><p>
<a href="functions.html#function_year"><code class="literal">YEAR('1999-06-21')</code></a> evaluates to
<code class="literal">1999</code>, which is found in partition
<code class="literal">d5</code>.
</p></li><li><p>
<span class="emphasis"><em>Scan only these two partitions and any partitions
that may lie between them</em></span>.
</p><p>
In this case, this means that only partitions
<code class="literal">d3</code>, <code class="literal">d4</code>, and
<code class="literal">d5</code> are scanned. The remaining partitions
may be safely ignored (and are ignored).
</p></li></ol></div><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
Invalid <code class="literal">DATE</code> and <code class="literal">DATETIME</code>
values referenced in the <code class="literal">WHERE</code> clause of a
query on a partitioned table are treated as
<code class="literal">NULL</code>. This means that a query such as
<code class="literal">SELECT * FROM
<em class="replaceable"><code>partitioned_table</code></em> WHERE
<em class="replaceable"><code>date_column</code></em> <
'2008-12-00'</code> does not return any values (see <a href="http://bugs.mysql.com/40972" target="_top">Bug#40972</a>).
</p></div><p>
So far, we have looked only at examples using
<code class="literal">RANGE</code> partitioning, but pruning can be applied
with other partitioning types as well.
</p><p>
Consider a table that is partitioned by <code class="literal">LIST</code>,
where the partitioning expression is increasing or decreasing,
such as the table <code class="literal">t3</code> shown here. (In this
example, we assume for the sake of brevity that the
<code class="literal">region_code</code> column is limited to values between
1 and 10 inclusive.)
</p><pre class="programlisting">CREATE TABLE t3 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
PARTITION r0 VALUES IN (1, 3),
PARTITION r1 VALUES IN (2, 5, 8),
PARTITION r2 VALUES IN (4, 9),
PARTITION r3 VALUES IN (6, 7, 10)
);
</pre><p>
For a query such as <code class="literal">SELECT * FROM t3 WHERE region_code
BETWEEN 1 AND 3</code>, the optimizer determines in which
partitions the values 1, 2, and 3 are found (<code class="literal">r0</code>
and <code class="literal">r1</code>) and skips the remaining ones
(<code class="literal">r2</code> and <code class="literal">r3</code>).
</p><p>
For tables that are partitioned by <code class="literal">HASH</code> or
<code class="literal">KEY</code>, partition pruning is also possible in
cases in which the <code class="literal">WHERE</code> clause uses a simple
<code class="literal">=</code> relation against a column used in the
partitioning expression. Consider a table created like this:
</p><pre class="programlisting">CREATE TABLE t4 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;
</pre><p>
Any query such as this one can be pruned:
</p><pre class="programlisting">SELECT * FROM t4 WHERE region_code = 7;
</pre><p>
Pruning can also be employed for short ranges, because the
optimizer can turn such conditions into <code class="literal">IN</code>
relations. For example, using the same table <code class="literal">t4</code>
as defined previously, queries such as these can be pruned:
</p><pre class="programlisting">SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;
</pre><p>
In both these cases, the <code class="literal">WHERE</code> clause is
transformed by the optimizer into <code class="literal">WHERE region_code IN
(3, 4, 5)</code>.
</p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
This optimization is used only if the range size is smaller than
the number of partitions. Consider this query:
</p><pre class="programlisting">SELECT * FROM t4 WHERE region_code BETWEEN 4 AND 8;
</pre><p>
The range in the <code class="literal">WHERE</code> clause covers 5 values
(4, 5, 6, 7, 8), but <code class="literal">t4</code> has only 4
partitions. This means that the previous query cannot be pruned.
</p></div><p>
Pruning can be used only on integer columns of tables partitioned
by <code class="literal">HASH</code> or <code class="literal">KEY</code>. For example,
this query on table <code class="literal">t4</code> cannot use pruning
because <code class="literal">dob</code> is a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> column:
</p><pre class="programlisting">SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';
</pre><p>
However, if the table stores year values in an
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a> column, then a query having
<code class="literal">WHERE year_col >= 2001 AND year_col <=
2005</code> can be pruned.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="partitioning-limitations"></a>18.5. Restrictions and Limitations on Partitioning</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="partitioning.html#partitioning-limitations-partitioning-keys-unique-keys">18.5.1. Partitioning Keys, Primary Keys, and Unique Keys</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-limitations-storage-engines">18.5.2. Partitioning Limitations Relating to Storage Engines</a></span></dt><dt><span class="section"><a href="partitioning.html#partitioning-limitations-functions">18.5.3. Partitioning Limitations Relating to Functions</a></span></dt></dl></div><p>
This section discusses current restrictions and limitations on
MySQL partitioning support, as listed here:
</p><a class="indexterm" name="id4828166"></a><div class="itemizedlist"><ul type="disc"><li><p><b>Prohibited constructs. </b>
Beginning with MySQL 5.1.12, the following constructs are
not permitted in partitioning expressions:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Stored functions, stored procedures, UDFs, or plugins.
</p></li><li><p>
Declared variables or user variables.
</p></li></ul></div><p>
For a list of SQL functions which are permitted in
partitioning expressions, see
<a href="partitioning.html#partitioning-limitations-functions" title="18.5.3. Partitioning Limitations Relating to Functions">Section 18.5.3, “Partitioning Limitations Relating to Functions”</a>.
</p></li><li><p><b>Arithmetic and logical operators. </b>
<a class="indexterm" name="id4828228"></a>
<a class="indexterm" name="id4828241"></a>
Use of the arithmetic operators
<a href="functions.html#operator_plus"><code class="literal">+</code></a>,
<a href="functions.html#operator_minus"><code class="literal">–</code></a>, and
<a href="functions.html#operator_times"><code class="literal">*</code></a> is
permitted in partitioning expressions. However, the result
must be an integer value or <code class="literal">NULL</code> (except
in the case of <code class="literal">[LINEAR] KEY</code> partitioning,
as discussed elswhere in this chapter — see
<a href="partitioning.html#partitioning-types" title="18.2. Partition Types">Section 18.2, “Partition Types”</a>, for more information).
</p><p>
Beginning with MySQL 5.1.23, the
<a href="functions.html#operator_div"><code class="literal">DIV</code></a> operator is also supported,
and the <a href="functions.html#operator_divide"><code class="literal">/</code></a>
operator is disallowed. (<a href="http://bugs.mysql.com/30188" target="_top">Bug#30188</a>, <a href="http://bugs.mysql.com/33182" target="_top">Bug#33182</a>)
</p><p>
Beginning with MySQL 5.1.12, the bit operators
<a href="functions.html#operator_bitwise-or"><code class="literal">|</code></a>,
<a href="functions.html#operator_bitwise-and"><code class="literal">&</code></a>,
<a href="functions.html#operator_bitwise-xor"><code class="literal">^</code></a>,
<a href="functions.html#operator_left-shift"><code class="literal"><<</code></a>,
<a href="functions.html#operator_right-shift"><code class="literal">>></code></a>,
and <a href="functions.html#operator_bitwise-invert"><code class="literal">~</code></a>
are not permitted in partitioning expressions.
</p></li><li><p><b>Server SQL mode. </b><a class="indexterm" name="id4828380"></a>
Tables employing user-defined partitioning do not preserve
the SQL mode in effect at the time that they were created.
As discussed in <a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, the
results of many MySQL functions and operators may change
according to the server SQL mode. Therefore, a change in the
SQL mode at any time after the creation of partitioned
tables may lead to major changes in the behavior of such
tables, and could easily lead to corruption or loss of data.
For these reasons, <span class="emphasis"><em>it is strongly recommended that
you never change the server SQL mode after creating
partitioned tables</em></span>.
</p><p><b>Examples. </b>
The following examples illustrate some changes in behavior
of partitioned tables due to a change in the server SQL
mode:
</p><div class="orderedlist"><ol type="1"><li><p><b>Error handling. </b>
Suppose you create a partitioned table whose
partitioning expression is one such as
<code class="literal"><em class="replaceable"><code>column</code></em> DIV
0</code> or
<code class="literal"><em class="replaceable"><code>column</code></em> MOD
0</code>, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE tn (c1 INT)</code></strong>
-> <strong class="userinput"><code>PARTITION BY LIST(1 DIV c1) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES IN (NULL),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES IN (1)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.05 sec)
</pre><p>
The default behavior for MySQL is to return
<code class="literal">NULL</code> for the result of a division
by zero, without producing any errors:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT @@SQL_MODE;</code></strong>
+------------+
| @@SQL_MODE |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO tn VALUES (NULL), (0), (1);</code></strong>
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
</pre><p>
However, changing the server SQL mode to treat
division by zero as an error and to enforce strict
error handling causes the same
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statement to
fail, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO tn VALUES (NULL), (0), (1);</code></strong>
<span class="errortext">ERROR 1365 (22012): Division by 0</span>
</pre><p>
</p></li><li><p><b>Table accessibility. </b>
Sometimes a change in the server SQL mode can make
partitioned tables unusable. The following
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
statement can be executed successfully only if the
<a href="server-administration.html#sqlmode_no_unsigned_subtraction"><code class="literal">NO_UNSIGNED_SUBTRACTION</code></a>
mode is in effect:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT @@SQL_MODE;</code></strong>
+------------+
| @@SQL_MODE |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> <strong class="userinput"><code>CREATE TABLE tu (c1 BIGINT UNSIGNED)</code></strong>
-> <strong class="userinput"><code>PARTITION BY RANGE(c1 - 10) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES LESS THAN (-5),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES LESS THAN (0),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES LESS THAN (5),</code></strong>
-> <strong class="userinput"><code>PARTITION p3 VALUES LESS THAN (10),</code></strong>
-> <strong class="userinput"><code>PARTITION p4 VALUES LESS THAN (MAXVALUE)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
<span class="errortext">ERROR 1563 (HY000): Partition constant is out of partition function domain</span>
mysql> <strong class="userinput"><code>SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT @@SQL_MODE;</code></strong>
+-------------------------+
| @@SQL_MODE |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)
mysql> <strong class="userinput"><code>CREATE TABLE tu (c1 BIGINT UNSIGNED)</code></strong>
-> <strong class="userinput"><code>PARTITION BY RANGE(c1 - 10) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES LESS THAN (-5),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES LESS THAN (0),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES LESS THAN (5),</code></strong>
-> <strong class="userinput"><code>PARTITION p3 VALUES LESS THAN (10),</code></strong>
-> <strong class="userinput"><code>PARTITION p4 VALUES LESS THAN (MAXVALUE)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.05 sec)
</pre><p>
If you remove the
<a href="server-administration.html#sqlmode_no_unsigned_subtraction"><code class="literal">NO_UNSIGNED_SUBTRACTION</code></a>
server SQL mode after creating
<code class="literal">tu</code>, you may no longer be able to
access this table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET SQL_MODE='';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM tu;</code></strong>
<span class="errortext">ERROR 1563 (HY000): Partition constant is out of partition function domain</span>
mysql> <strong class="userinput"><code>INSERT INTO tu VALUES (20);</code></strong>
<span class="errortext">ERROR 1563 (HY000): Partition constant is out of partition function domain</span>
</pre><p>
</p></li></ol></div><p>
</p></li><li><p><b>Performance considerations. </b>
</p><div class="itemizedlist"><ul type="circle"><li><p><b>File system operations. </b>
Partitioning and repartitioning operations (such as
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> with
<code class="literal">PARTITION BY ...</code>,
<code class="literal">REORGANIZE PARTITIONS</code>, or
<code class="literal">REMOVE PARTITIONING</code>) depend on
file system operations for their implementation.
This means that the speed of these operations is
affected by such factors as file system type and
characteristics, disk speed, swap space, file
handling efficiency of the operating system, and
MySQL server options and variables that relate to
file handling. In particular, you should make sure
that
<a href="server-administration.html#sysvar_large_files_support"><code class="literal">large_files_support</code></a>
is enabled and that
<a href="server-administration.html#sysvar_open_files_limit"><code class="literal">open_files_limit</code></a> is
set properly. For partitioned tables using the
<code class="literal">MyISAM</code> storage engine, increasing
<a href="server-administration.html#sysvar_myisam_max_sort_file_size"><code class="literal">myisam_max_sort_file_size</code></a>
may improve performance; partitioning and
repartitioning operations involving
<code class="literal">InnoDB</code> tables may be made more
efficient by enabling
<a href="storage-engines.html#sysvar_innodb_file_per_table"><code class="literal">innodb_file_per_table</code></a>.
</p></li><li><p><b>Table locks. </b>
The process executing a partitioning operation on a
table takes a write lock on the table. Reads from
such tables are relatively unaffected; pending
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> and
<a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a> operations are
performed as soon as the partitioning operation has
completed.
</p></li><li><p><b>Storage engine. </b>
Partitioning operations, queries, and update
operations generally tend to be faster with
<code class="literal">MyISAM</code> tables than with
<code class="literal">InnoDB</code> or
<code class="literal">NDB</code> tables.
</p></li><li><p><b>Use of indexes and partition pruning. </b>
As with nonpartitioned tables, proper use of indexes
can speed up queries on partitioned tables
significantly. In addition, designing partitioned
tables and queries on these tables to take advantage
of <em class="firstterm">partition pruning</em> can
improve performance dramatically. See
<a href="partitioning.html#partitioning-pruning" title="18.4. Partition Pruning">Section 18.4, “Partition Pruning”</a>, for more
information.
</p></li><li><p><b>Performance with <code class="literal">LOAD DATA</code>. </b>
Prior to MySQL 5.1.23, <a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD
DATA</code></a> performed very poorly when importing
into partitioned tables. The statement now uses
buffering to improve performance; however, the
buffer uses 130 KB memory per partition to achieve
this. (<a href="http://bugs.mysql.com/26527" target="_top">Bug#26527</a>)
</p></li></ul></div><p>
</p></li><li><p><b>Maximum number of partitions. </b><a class="indexterm" name="id4829003"></a>
The maximum possible number of partitions for a given table
is 1024. This includes subpartitions.
</p><p>
If, when creating tables with a large number of partitions
(but less than the maximum), you encounter an error message
such as <span class="errortext">Got error 24 from storage
engine</span>, you may be able to address the issue by
increasing the value of the
<a href="server-administration.html#sysvar_open_files_limit"><code class="literal">open_files_limit</code></a> system
variable. However, this is dependent on the operating system,
and may not be possible or advisable on all platforms; see
<a href="error-handling.html#not-enough-file-handles" title="B.1.2.18. 'File' Not Found and
Similar Errors">Section B.1.2.18, “<span class="errortext">'<em class="replaceable"><code>File</code></em>' Not Found</span> and
Similar Errors”</a>, for more
information. In some cases, using large numbers (hundreds) of
partitions may also not be advisable due to other concerns, so
using more partitions does not automatically lead to better
results.
</p></li><li><p><b>Foreign keys not supported. </b><a class="indexterm" name="id4829054"></a>
Partitioned tables do not support foreign keys. This means
that:
</p><div class="orderedlist"><ol type="1"><li><p>
Definitions of tables employing user-defined
partitioning may not contain foreign key references to
other tables.
</p></li><li><p>
No table definition may contain a foreign key
reference to a partitioned table.
</p></li></ol></div><p>
The scope of these restrictions includes tables that use the
<code class="literal">InnoDB</code> storage engine.
</p></li><li><p><b><code class="literal">ALTER TABLE ... ORDER BY</code>. </b>
An <code class="literal">ALTER TABLE ... ORDER BY
<em class="replaceable"><code>column</code></em></code> statement run
against a partitioned table causes ordering of rows only
within each partition.
</p></li><li><p><b>FULLTEXT indexes. </b><a class="indexterm" name="id4829138"></a>
Partitioned tables do not support
<code class="literal">FULLTEXT</code> indexes. This includes
partitioned tables employing the <code class="literal">MyISAM</code>
storage engine.
</p></li><li><p><b>Spatial columns. </b>
Columns with spatial data types such as
<code class="literal">POINT</code> or <code class="literal">GEOMETRY</code>
cannot be used in partitioned tables.
</p></li><li><p><b>Temporary tables. </b><a class="indexterm" name="id4829202"></a>
As of MySQL 5.1.8, temporary tables cannot be partitioned.
(<a href="http://bugs.mysql.com/17497" target="_top">Bug#17497</a>)
</p></li><li><p><b>Log tables. </b>
Beginning with MySQL 5.1.20, it is no longer possible to
partition the log tables; beginning with that version, an
<code class="literal">ALTER TABLE ... PARTITION BY ...</code>
statement on such a table fails with an error. (<a href="http://bugs.mysql.com/27816" target="_top">Bug#27816</a>)
</p></li><li><p><b>Data type of partitioning key. </b><a class="indexterm" name="id4829262"></a>
A partitioning key must be either an integer column or an
expression that resolves to an integer. The column or
expression value may also be <code class="literal">NULL</code>. (See
<a href="partitioning.html#partitioning-handling-nulls" title="18.2.6. How MySQL Partitioning Handles NULL">Section 18.2.6, “How MySQL Partitioning Handles <code class="literal">NULL</code>”</a>.)
</p><p>
The lone exception to this restriction occurs when
partitioning by [<code class="literal">LINEAR</code>]
<code class="literal">KEY</code>, where it is possible to use columns of
other types as partitioning keys, because MySQL's internal
key-hashing functions produce the correct data type from these
types. For example, the following <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> statement is valid:
</p><pre class="programlisting">CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;
</pre><p>
This exception does <span class="emphasis"><em>not</em></span> apply to
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column types.
</p></li><li><p><b>Subqueries. </b><a class="indexterm" name="id4829352"></a>
A partitioning key may not be a subquery, even if that
subquery resolves to an integer value or
<code class="literal">NULL</code>.
</p></li><li><p><b>Subpartitions. </b><a class="indexterm" name="id4829385"></a>
Subpartitions are limited to <code class="literal">HASH</code> or
<code class="literal">KEY</code> partitioning. <code class="literal">HASH</code>
and <code class="literal">KEY</code> partitions cannot be
subpartitioned.
</p></li><li><p><b>Key caches not supported. </b><a class="indexterm" name="id4829435"></a><a class="indexterm" name="id4829447"></a><a class="indexterm" name="id4829460"></a>
Key caches are not supported for partitioned tables. The
<a href="sql-syntax.html#cache-index" title="12.5.6.2. CACHE INDEX Syntax"><code class="literal">CACHE INDEX</code></a> and
<a href="sql-syntax.html#load-index" title="12.5.6.5. LOAD INDEX INTO
CACHE Syntax"><code class="literal">LOAD INDEX INTO
CACHE</code></a> statements, when you attempt to use them on
tables having user-defined partitioning, fail with the
errors <span class="errortext">The storage engine for the table doesn't
support assign_to_keycache</span> and <span class="errortext">The
storage engine for the table doesn't support
preload_keys</span>, respectively.
</p></li><li><p><b><code class="literal">DELAYED</code> option not supported. </b>
Use of <a href="sql-syntax.html#insert-delayed" title="12.2.5.2. INSERT DELAYED Syntax"><code class="literal">INSERT DELAYED</code></a> to
insert rows into a partitioned table is not supported.
Beginning with MySQL 5.1.23, attempting to do so fails with
an error. (<a href="http://bugs.mysql.com/31210" target="_top">Bug#31210</a>)
</p></li><li><p><b><code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX DIRECTORY</code>
options. </b>
<code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
DIRECTORY</code> are subject to the following
restrictions when used with partitioned tables:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Beginning with MySQL 5.1.23, table-level <code class="literal">DATA
DIRECTORY</code> and <code class="literal">INDEX
DIRECTORY</code> options are ignored. (<a href="http://bugs.mysql.com/32091" target="_top">Bug#32091</a>)
</p></li><li><p>
On Windows, the <code class="literal">DATA DIRECTORY</code> and
<code class="literal">INDEX DIRECTORY</code> options are not
supported for individual partitions or subpartitions
(<a href="http://bugs.mysql.com/30459" target="_top">Bug#30459</a>).
</p></li></ul></div><p>
</p></li><li><p><b>Repairing and rebuilding partitioned tables. </b>
The statements <a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a>,
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a>,
<a href="sql-syntax.html#analyze-table" title="12.5.2.1. ANALYZE TABLE Syntax"><code class="literal">ANALYZE TABLE</code></a>, and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> are supported
for partitioned tables beginning with MySQL 5.1.27. (See <a href="http://bugs.mysql.com/20129" target="_top">Bug#20129</a>.) <a href="programs.html#mysqlcheck" title="4.5.3. mysqlcheck — A Table Maintenance Program"><span><strong class="command">mysqlcheck</strong></span></a> and
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> are not supported with
partitioned tables.
</p><p>
In addition, you can use <code class="literal">ALTER TABLE ... REBUILD
PARTITION</code> to rebuild one or more partitions of a
partitioned table; <code class="literal">ALTER TABLE ... REORGANIZE
PARTITION</code> also causes partitions to be rebuilt. Both
of these statements were added in MySQL 5.1.5. See
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax">Section 12.1.7, “<code class="literal">ALTER TABLE</code> Syntax”</a>, for more information about
these two statements.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-limitations-partitioning-keys-unique-keys"></a>18.5.1. Partitioning Keys, Primary Keys, and Unique Keys</h3></div></div></div><a class="indexterm" name="id4829720"></a><a class="indexterm" name="id4829729"></a><a class="indexterm" name="id4829742"></a><a class="indexterm" name="id4829751"></a><a class="indexterm" name="id4829763"></a><p>
This section discusses the relationship of partitioning keys
with primary keys and unique keys. The rule governing this
relationship can be expressed as follows: All columns used in
the partitioning expression for a partitioned table must be part
of every unique key that the table may have.
</p><p>
In other words, <span class="emphasis"><em>every unique key on the table must use
every column in the table's partitioning expression</em></span>.
(This also includes the table's primary key, since it is by
definition a unique key. This particular case is discussed later
in this section.) For example, each of the following table
creation statements is invalid:
</p><pre class="programlisting">CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
</pre><p>
In each case, the proposed table would have at least one unique
key that does not include all columns used in the partitioning
expression.
</p><p>
Each of the following statements is valid, and represents one
way in which the corresponding invalid table creation statement
could be made to work:
</p><pre class="programlisting">CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
</pre><p>
This example shows the error produced in such cases:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t3 (</code></strong>
-> <strong class="userinput"><code>col1 INT NOT NULL,</code></strong>
-> <strong class="userinput"><code>col2 DATE NOT NULL,</code></strong>
-> <strong class="userinput"><code>col3 INT NOT NULL,</code></strong>
-> <strong class="userinput"><code>col4 INT NOT NULL,</code></strong>
-> <strong class="userinput"><code>UNIQUE KEY (col1, col2),</code></strong>
-> <strong class="userinput"><code>UNIQUE KEY (col3)</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>PARTITION BY HASH(col1 + col3)</code></strong>
-> <strong class="userinput"><code>PARTITIONS 4;</code></strong>
<span class="errortext">ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function</span>
</pre><p>
The <code class="literal">CREATE</code> statement fails because both
<code class="literal">col1</code> and <code class="literal">col3</code> are included
in the proposed partitioning key, but neither of these columns
is part of both of unique keys on the table. This shows one
possible fix for the invalid table definition;
</p><pre class="programlisting">mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2, col3),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col3)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)
</pre><p>
In this case, the proposed partitioning key
<code class="literal">col3</code> is part of both unique keys, and the
table creation statement succeeds.
</p><p>
Since every primary key is by definition a unique key, this
restriction also includes the table's primary key, if it has
one. For example, the next two statements are invalid:
</p><pre class="programlisting">CREATE TABLE t4 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
</pre><p>
In both cases, the primary key does not include all columns
referenced in the partitioning expression. However, both of the
next two statements are valid:
</p><pre class="programlisting">CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
</pre><p>
If a table has no unique keys — this includes having no
primary key — then this restriction does not apply, and
you may use any column or columns in the partitioning expression
as long as the column type is compatible with the partitioning
type.
</p><p>
For the same reason, you cannot later add a unique key to a
partitioned table unless the key includes all columns used by
the table's partitioning expression. Consider given the
partitioned table defined as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t_no_pk (c1 INT, c2 INT)</code></strong>
-> <strong class="userinput"><code>PARTITION BY RANGE(c1) (</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES LESS THAN (10),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES LESS THAN (20),</code></strong>
-> <strong class="userinput"><code>PARTITION p2 VALUES LESS THAN (30),</code></strong>
-> <strong class="userinput"><code>PARTITION p3 VALUES LESS THAN (40)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.12 sec)
</pre><p>
It is possible to add a primary key to
<code class="literal">t_no_pk</code> using either of these
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statements:
</p><pre class="programlisting"># possible PK
mysql> <strong class="userinput"><code>ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);</code></strong>
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> <strong class="userinput"><code>ALTER TABLE t_no_pk DROP PRIMARY KEY;</code></strong>
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
# use another possible PK
mysql> <strong class="userinput"><code>ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);</code></strong>
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> <strong class="userinput"><code>ALTER TABLE t_no_pk DROP PRIMARY KEY;</code></strong>
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
</pre><p>
However, the next statement fails, because <code class="literal">c1</code>
is part of the partitioning key, but is not part of the proposed
primary key:
</p><pre class="programlisting"># fails with error 1503
mysql> <strong class="userinput"><code>ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);</code></strong>
<span class="errortext">ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function</span>
</pre><p>
Since <code class="literal">t_no_pk</code> has only <code class="literal">c1</code>
in its partitioning expression, attempting to adding a unique
key on <code class="literal">c2</code> alone fails. However, you can add a
unique key that uses both <code class="literal">c1</code> and
<code class="literal">c2</code>.
</p><p>
These rules also apply to existing nonpartitioned tables that
you wish to partition using <code class="literal">ALTER TABLE ... PARTITION
BY</code>. Consider a table <code class="literal">np_pk</code> defined
as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE np_pk (</code></strong>
-> <strong class="userinput"><code>id INT NOT NULL AUTO_INCREMENT,</code></strong>
-> <strong class="userinput"><code>name VARCHAR(50),</code></strong>
-> <strong class="userinput"><code>added DATE,</code></strong>
-> <strong class="userinput"><code>PRIMARY KEY (id)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
Query OK, 0 rows affected (0.08 sec)
</pre><p>
The following <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
statements fails with an error, because the
<code class="literal">added</code> column is not part of any unique key in
the table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE np_pk</code></strong>
-> <strong class="userinput"><code>PARTITION BY HASH( TO_DAYS(added) )</code></strong>
-> <strong class="userinput"><code>PARTITIONS 4;</code></strong>
<span class="errortext">ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function</span>
</pre><p>
However, this statement using the <code class="literal">id</code> column
for the partitioning column is valid, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE np_pk</code></strong>
-> <strong class="userinput"><code>PARTITION BY HASH(id)</code></strong>
-> <strong class="userinput"><code>PARTITIONS 4;</code></strong>
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
</pre><p>
In the case of <code class="literal">np_pk</code>, the only column that
may be used as part of a partitioning expression is
<code class="literal">id</code>; if you wish to partition this table using
any other column or columns in the partitioning expression, you
must first modify the table, either by adding the desired column
or columns to the primary key, or by dropping the primary key
altogether.
</p><p>
We are working to remove this limitation in a future MySQL
release series.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-limitations-storage-engines"></a>18.5.2. Partitioning Limitations Relating to Storage Engines</h3></div></div></div><a class="indexterm" name="id4830302"></a><p>
The following limitations apply to the use of storage engines
with user-defined partitioning of tables.
</p><p><b><code class="literal">MERGE</code> storage engine. </b>
User-defined partitioning and the <code class="literal">MERGE</code>
storage engine are not compatible. Tables using the
<code class="literal">MERGE</code> storage engine cannot be partitioned.
Partitioned tables cannot be merged.
</p><p><b><code class="literal">FEDERATED</code> storage engine. </b>
Partitioning of <code class="literal">FEDERATED</code> tables is not
supported. Beginning with MySQL 5.1.15, it is not possible to
create partitioned <code class="literal">FEDERATED</code> tables at all.
We are working to remove this limitation in a future MySQL
release.
</p><p><b><code class="literal">CSV</code> storage engine. </b>
Partitioned tables using the <code class="literal">CSV</code> storage
engine are not supported. Starting with MySQL 5.1.12, it is
not possible to create partitioned <code class="literal">CSV</code>
tables at all.
</p><p><b><code class="literal">BLACKHOLE</code> storage engine. </b>
Prior to MySQL 5.1.6, tables using the
<code class="literal">BLACKHOLE</code> storage engine also could not be
partitioned.
</p><p><b><code class="literal">NDBCLUSTER</code> storage engine (MySQL Cluster). </b>
Partitioning by <code class="literal">KEY</code> (or <code class="literal">LINEAR
KEY</code>) is the only type of partitioning supported for
the <code class="literal">NDBCLUSTER</code> storage engine.
Beginning with MySQL 5.1.12, it is not possible to create a
MySQL Cluster table using any partitioning type other than
[<code class="literal">LINEAR</code>] <code class="literal">KEY</code>, and
attempting to do so fails with an error.
</p><p>
In addition, the maximum number of partitions that can be
defined for an <code class="literal">NDBCLUSTER</code> table is
8 times the number of node groups in the cluster. (See
<a href="mysql-cluster.html#mysql-cluster-nodes-groups" title="17.1.2. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions">Section 17.1.2, “MySQL Cluster Nodes, Node Groups, Replicas, and Partitions”</a>, for more
information about node groups in MySQL Cluster.)
</p><p>
Beginning with MySQL Cluster NDB 6.2.18, MySQL Cluster NDB
6.3.25, and MySQL Cluster NDB 7.0.6, <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> and <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
statements that would cause a user-partitioned
<code class="literal">NDBCLUSTER</code> table not to meet either
or both of the following two requirements are disallowed, and
fail with an error (<a href="http://bugs.mysql.com/40709" target="_top">Bug#40709</a>):
</p><div class="orderedlist"><ol type="1"><li><p>
The table must have an explicit primary key.
</p></li><li><p>
All columns listed in the table's partitioning
expression must be part of the primary key.
</p></li></ol></div><p><b>Exception. </b>
If a user-partitioned <code class="literal">NDBCLUSTER</code>
table is created using an empty column-list (that is, using
<code class="literal">PARTITION BY KEY()</code> or <code class="literal">PARTITION BY
LINEAR KEY()</code>), then no explicit primary key is
required.
</p><p><b>Upgrading partitioned tables. </b>
When performing an upgrade, tables which are partitioned by
<code class="literal">KEY</code> and which use any storage engine other
than <code class="literal">NDBCLUSTER</code> must be dumped
and reloaded.
</p><p><b>Same storage engine for all partitions. </b>
All partitions of a partitioned table must use the same
storage engine and it must be the same storage engine used by
the table as a whole. In addition, if one does not specify an
engine on the table level, then one must do either of the
following when creating or altering a partitioned table:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Do <span class="emphasis"><em>not</em></span> specify any engine for
<span class="emphasis"><em>any</em></span> partition or subpartition
</p></li><li><p>
Specify the engine for <span class="emphasis"><em>all</em></span>
partitions or subpartitions
</p></li></ul></div><p>
We are working to remove this limitation in a future MySQL
release.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="partitioning-limitations-functions"></a>18.5.3. Partitioning Limitations Relating to Functions</h3></div></div></div><p>
This section discusses limitations in MySQL Partitioning
relating specifically to functions used in partitioning
expressions.
</p><a class="indexterm" name="id4830662"></a><p>
Beginning with MySQL 5.1.12, only the MySQL functions shown in
the following table are supported in partitioning expressions:
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><a href="functions.html#function_abs"><code class="literal">ABS()</code></a></td><td><a href="functions.html#function_ceiling"><code class="literal">CEILING()</code></a> (see
<em class="citetitle"><code class="literal">CEILING()</code> and
<code class="literal">FLOOR()</code></em>, immediately
following this list)</td><td><a href="functions.html#function_day"><code class="literal">DAY()</code></a></td></tr><tr><td><a href="functions.html#function_dayofmonth"><code class="literal">DAYOFMONTH()</code></a></td><td><a href="functions.html#function_dayofweek"><code class="literal">DAYOFWEEK()</code></a></td><td><a href="functions.html#function_dayofyear"><code class="literal">DAYOFYEAR()</code></a></td></tr><tr><td><a href="functions.html#function_datediff"><code class="literal">DATEDIFF()</code></a></td><td><a href="functions.html#function_extract"><code class="literal">EXTRACT()</code></a></td><td><a href="functions.html#function_floor"><code class="literal">FLOOR()</code></a> (see
<em class="citetitle"><code class="literal">CEILING()</code> and
<code class="literal">FLOOR()</code></em>, immediately
following this list)</td></tr><tr><td><a href="functions.html#function_hour"><code class="literal">HOUR()</code></a></td><td><a href="functions.html#function_microsecond"><code class="literal">MICROSECOND()</code></a></td><td><a href="functions.html#function_minute"><code class="literal">MINUTE()</code></a></td></tr><tr><td><a href="functions.html#function_mod"><code class="literal">MOD()</code></a></td><td><a href="functions.html#function_month"><code class="literal">MONTH()</code></a></td><td><a href="functions.html#function_quarter"><code class="literal">QUARTER()</code></a></td></tr><tr><td><a href="functions.html#function_second"><code class="literal">SECOND()</code></a></td><td><a href="functions.html#function_time-to-sec"><code class="literal">TIME_TO_SEC()</code></a></td><td><a href="functions.html#function_to-days"><code class="literal">TO_DAYS()</code></a></td></tr><tr><td><a href="functions.html#function_weekday"><code class="literal">WEEKDAY()</code></a></td><td><a href="functions.html#function_year"><code class="literal">YEAR()</code></a></td><td><a href="functions.html#function_yearweek"><code class="literal">YEARWEEK()</code></a></td></tr></tbody></table></div><p><b><code class="literal">CEILING()</code> and
<code class="literal">FLOOR()</code>. </b>
Each of these functions returns an integer only if it is
passed an integer argument. This means, for example, that the
following <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
statement fails with an error, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(</code></strong>
-> <strong class="userinput"><code>PARTITION p0 VALUES IN (1,3,5),</code></strong>
-> <strong class="userinput"><code>PARTITION p1 VALUES IN (2,4,6)</code></strong>
-> <strong class="userinput"><code>);</code></strong>
<span class="errortext">ERROR 1490 (HY000): The PARTITION function returns the wrong type</span>
</pre><p>
See <a href="functions.html#mathematical-functions" title="11.5.2. Mathematical Functions">Section 11.5.2, “Mathematical Functions”</a>, for more
information about the return types of these functions.
</p></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="mysql-cluster.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="stored-programs-views.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 17. MySQL Cluster NDB 6.X/7.X </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 19. Stored Programs and Views</td></tr></table></div></body></html>
