MySQL in a Nutshell [57]
ALTER TABLE: Partition altering clauses
ALTER [IGNORE] TABLE table
PARTITION BY options |
ADD PARTITION (definition) |
COALESCE PARTITION number |
DROP PARTITION partition |
REORGANIZE PARTITION partition INTO (definition) |
REMOVE PARTITIONING
These table partition clauses for ALTER TABLE may be used to add or remove partitions in a table. They were added as of version 5.1.6 of MySQL. For partition clauses that analyze, check, optimize, rebuild, and repair partitions in a table, see the next subsection (ALTER TABLE: Partition administration clauses”). Also, see the CREATE TABLE statement explanation for more information on table partitioning.
It should be noted that the execution of the partition clauses for ALTER TABLE is very slow. You may not want to use them with data that is in use if you can avoid it. Instead, you might deploy a method of locking the table to be partitioned for read-only activities, making a copy of the table, partitioning the new table, and switching the new table with the old one, but keeping the old table as a backup copy in case there are problems.
This section includes several examples of partitioning a MyISAM table. The partition clauses are explained as they are used in each example. Partitioning is visible at the filesystem level, so to start, let’s look at a table’s files:
ls -1 clients*
clients.frm
clients.MYD
clients.MYI
We used the ls command (because this server is running Linux) at the command line to get a directory listing of the files for the clients table, in the sales_dept database subdirectory, in the data directory for MySQL. You can see the usual three file types for a MyISAM table.
The PARTITION BY clause can be used to initially partition a table with the ALTER TABLE statement. Any partition options used with the same clause in the CREATE TABLE statement may be used in ALTER TABLE. See the definition of the CREATE TABLE statement later in this chapter for more options.
In the following example, we alter the table clients using this clause to create partitions:
ALTER TABLE clients
PARTITION BY KEY(client_id)
PARTITIONS 2;
In this statement, we are instructing MySQL to partition the given table by the KEY method using the client_id column. We further tell it to split the table into two partitions. Now, let’s run the ls command again to see the results at the filesystem level:
ls -1 clients*
clients.frm
clients.par
clients#P#p0.MYD
clients#P#p0.MYI
clients#P#p1.MYD
clients#P#p1.MYI
As you can see, we now have a pair of index and datafiles for each partition, along with another file related to the partition schema (i.e., the .par file). The table schema file (i.e., the .frm file) remains unchanged.
The ADD PARTITION clause adds a new partition to a table in which partitions are determined based on a range of values. To demonstrate this, let’s partition the clients table again, but this time we’ll base the partitioning on a range of values for the client_id column, the primary key. If a table has a primary key, that key must be included in the basis of the partitions:
ALTER TABLE clients
ADD PARTITION (PARTITION p2);
The REMOVE PARTITIONING clause removes partitioning from a table. It shifts data back to one datafile and one index file. Here is an example of its use:
ALTER TABLE clients
REMOVE PARTITIONING;
For some situations, the ADD PARTITION clause discussed previously won’t work. In particular, it won’t work with a table in which the last partition was given the range of MAXVALUE:
ALTER TABLE clients
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN MAXVALUE);
ALTER TABLE clients
ADD PARTITION (PARTITION p2 VALUES LESS THAN (800));
ERROR 1481 (HY000):
VALUES LESS THAN value must be strictly increasing for each partition
Instead of ADD PARTITION, the REORGANIZE PARTITION clause can be used to split the