About the Authors

Jon Stephens is the co-author of MySQL Database Design and Optimization and PHP 5 Recipes. He joined the MySQL Documentation Team in 2004, and lives in Australia.

Peter Gulutzan is a Software Architect at MySQL AB, and author of SQL Performance Tuning. He's also another Canadian member of the MySQL Documentation Team.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

MySQL 5.1 New Features: MySQL Partitions

Partitioning is a way of pre-organizing table storage. You can say "some of the table's rows will go here, some will go there, still others will go to to still other places". Often, depending on the storage engine, the effect is to spread the table's rows over different files or even different disks.

We - one of the folks who write the MySQL Reference Manual and one of the folks who test new features - will try to touch on everything that we, or beta testers, or participants in the the MySQL forums, have ever considered is worth touching on about partitioning. Nevertheless we won't repeat what's already in the MySQL Reference Manual when we can't think of a different way to say it.

We'll show examples and test results that we ran with the version that's latest at the time we're writing this - MySQL 5.1.12 BETA.

Grammar Description That's Expressed Differently From The Manual

Partitioning introduces some new SQL syntax for MySQL. Fortunately, you won't have to worry about learning anything new for data manipulation statements like SELECT, UPDATE, INSERT, DELETE, or REPLACE to work with partitioned tables. However, there are several additions to the data definition statements - CREATE TABLE and ALTER TABLE - that you'll need to know in order to create partitioned tables and to alter them once they've been created.

Creating Partitioned Tables

The key to creating partitioned tables is the PARTITION BY clause of CREATE TABLE, which specifies the type of partitioning to be used. PARTTITION BY is followed by the keyword giving the partitioning type, for which there are four options: RANGE, LIST, HASH, and KEY. The partitioning type is followed by a partitioning expression in parentheses; depending on the type of partitioning you use and your requirements, this expression may be the name of a table column, a simple mathematical expression involving one or more columns, or a comma-separated list of column names. For tables partitioned by RANGE, LIST, or HASH (including LINEAR HASH), this expression must evaluate to an integer value or NULL; for a table partitioned by KEY, you simply supply the names of zero or more columns, and these may be of any datatype.

Let's look briefly at each partitioning type.

RANGE: Rows with column or expression values falling within a specified range are assigned to a given partition. For example, suppose you've a table storing data relating to orders, and you'd like to specify that orders with IDs less than 10,000 be stored in one partition, orders whose IDs are between 10,000 and 19,999 in another, orders with IDs between 20,000 and 29,999 in another, and so on. RANGE partitioning is a good fit for such a partitioning scheme.

LIST: Allows you to specify lists of values for each partition such that rows with matching column values are stored in the corresponding partition. For instance, let's say each of your orders is placed at one of several different stores organized into 4 districts, and you'd like to store orders placed at each district in the same partition. Using this type of partitioning allows you to say, "Stores 1, 3, and 6 are in District 1, and I'd like those orders to be in their own partition; Stores 2, 5, 12, and 14 are in District 2, and I'd like these to be in another partition of their own [and so on]".

HASH: This type of partitioning is useful when you don't care which rows end up in which partitions, but rather that the rows are evenly distributed among all partitions. This distribution is based on an expression - known as a hashing function or hashing expression - that you supply when creating the table.

KEY: This type of partitioning is similar to KEY, except that MySQL uses its own hashing expression. Unlike the other types of partitioning, you aren't required to use an expression whose value is an integer or NULL.

HASH and KEY partitioning in addition each have a LINEAR variant. The difference is that, when you use LINEAR HASH or LINEAR KEY partitioning, the distribution of the data is done in such a way as to increase the speed of partition manipulation operations - that is, the adding, dropping, merging or splitting partitions (more about these operations later). However, the rows are not distributed as evenly as with regular HASH or KEY partitioning. (If you're interested in learning about the difference in the algorithm used for LINEAR partitioning by HASH and KEY, see http://dev.mysql.com/doc/refman/5.1/en/partitioning-linear-hash.html.)

For creating tables that are partitioned by RANGE and LIST, you must supply a definition for each partition that shows which values are to be matched. For tables partitioned by [LINEAR] HASH or [LINEAR] KEY, all that's necessary is to tell MySQL how many partitions you want.

Let's look at some examples using the following (non-partitioned) table as a basis:

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM;

Previously, we talked about a scenario where we wanted to partition this table based on the order ID number, with rows representing orders having IDs less than 10,000 going into one partition, those with ID values between 10,000 and 19,999 going into another partition, and so on. In other words, we want to partition the table based on contiguous ranges of values, from which RANGE partitioning gets its name. Here's how we can do this:

CREATE TABLE orders_range (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000)
);

Notice that the PARTITION BY clause comes after everything else in the CREATE TABLE statement, including the ENGINE clause. Speaking of storage engines, this is perhaps a good place to mention that you cannot partition CSV or MERGE tables. Nor can you partition temporary tables.

Since the id column can contain only an integer, we can use its value without modification, so the PARTITION BY clause for this table is simply PARTITION BY RANGE(id). This is followed by a set of partition definitions, in parentheses, separated by commas. The definition of each partition begins with the PARTITION keyword, followed by a name for the partition. Each partition in a partitioned table must have a name, and the name must be unique among all of that table's partitions.

This is followed by a VALUES LESS THAN clause and a value in parentheses. What this means is, "any row whose partitioning expression value is less than x, that isn't matched by a previous partition's definition, should be stored in this partition". In other words, any row having an id whose value is less than 10,000 is stored in partition p0. The next partition is named p1, and stores rows whose id column values are less than 20,000 - and since rows with id column values less than 10,000 are already being stored in partition p0, the smallest id column value that will be stored in p1 is 10,000. If p1 were the first partition to be defined for this table, then all rows whose id column value was less than 20,000 would be stored there, and not just those rows having an id between 10,000 and 19,999.

Similarly, partitions p2, p3, and p4 store rows as shown here:

partition   id less than...   and greater than...
---------   ---------------   -------------------
p2          30,000            19,999
p3          40,000            29,999
p4          50,000            39,999

A natural question to ask at this point might be, "But what happens if I try to insert a row having order ID number 50,000 or higher?" The answer is that such an insert fails:

mysql> INSERT INTO orders_list VALUES
    -> (50000, 117, 5, 323, '2006-07-30', 'Wow! Our 50,000th order!');
ERROR 1513 (HY000): Table has no partition for value 50000

While there may be cases where you wouldn't want an out-of-range value to be inserted, let's assume that this isn't one of them. One way to avoid this problem is to keep a careful eye on this table, and when you see that you're getting close to 50,000 in the id column, add a new partition to the table (we'll talk about how you'd do this later on). If you're anything like the authors of this article, you're probably not too keen on scheduling your lunch breaks or vacations around such events. Wouldn't it be better if you created a "catch-all" partition with an impossibly high value for the upper end of the range? Such a partition might be defined like this:

PARTITION p5 VALUES LESS THAN(1000000)

That would work for a while, but sooner or later, you'd receive that 1,000,000th order, and that Error 1513 to contend with - most likely at the worst possible time (as such events are wont to occur). However, there's a more general way to accomplish this, as you can see here:

CREATE TABLE orders_range2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000),
    PARTITION p5 VALUES LESS THAN(MAXVALUE)
);

The definition for partition p5 looks a bit different, doesn't it? Its VALUES LESS THAN clause uses a special keyword MAXVALUE, which means "the greatest possible value of this type". Using MAXVALUE lets us create a "catch-all" partition that takes care of any values greater than the largest one that we've defined. In this case, partition p5 stores any row having an id column value equal to or greater than 50,000, no matter how large it might be, up to the greatest value that the column can accommodate.

IMPORTANT: The order of the partitions in tables orders_range and orders_range_2 is not accidental - RANGE partitions must be defined in the order of their VALUES LESS THAN values, going from lowest to highest. Only one RANGE partition may use MAXVALUE, and such a partition must be defined last. Trying to define RANGE partitions in any other order results in an error.

TIP: NULL is considered to be less than any other value, so if you insert a row whose id column value is NULL into orders_range or orders_range2, that row will be stored in partition p0. (Of course, if we had defined the id column as NOT NULL, this wouldn't apply.)

RANGE partitioning can be useful in speeding up selects on large tables, as you'll see later in this article. It can also be handy for situations where date and/or time values are important. Let's look at an example of the latter, which also illustrates a partitioning expression that uses a function. Suppose that the orders table contains order data covering several years, and you'd like to divide it into partitions such that each partition stores data for orders from a 5-year span. Here's one way you can do this:

CREATE TABLE orders_range3 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE( YEAR(order_date) ) (
    PARTITION p0 VALUES LESS THAN(1990),
    PARTITION p1 VALUES LESS THAN(1995),
    PARTITION p2 VALUES LESS THAN(2000),
    PARTITION p3 VALUES LESS THAN(2005),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

If you tried to create this table using PARTITION BY RANGE(order_date), the CREATE TABLE statement would fail with the error "The PARTITION function returns the wrong type" because the order_date column's datatype isn't one of MySQL's integer types. So we use the YEAR() function, which gives us an integer value. We'll expand on this later in this article when we discuss date-based partitioning.

You may also have noticed that the orders_range3 table has no primary key; instead, we created the id column as INT AUTO_INCREMENT. This is because we wanted to use a different column as the basis for partitioning, and any column used for a partitioning expression must be part of the table's primary key (if it has one) or (first) unique key (if it has a unique key but no primary key). Otherwise, you'll see the error message, "A PRIMARY KEY needs to include all fields in the partition function". A number of people have been confused by this. Here are the rules you need to remember with with regard to table keys and indexes vis à vis partitioning expressions:

If a table has a primary key, then any columns used in the partitioning expression must be part of the primary key.
If a table has no primary key, but does have one or more unique keys, then any columns used in the partitioning expression must be part of the (first) unique key.
Regular indexes have no effect on what columns may or may not be used in a partitioning expression. This means that if you need an index on the id column (or any other column) to speed up searches, you can define one without getting into trouble.
Partitioned tables do not support FULLTEXT indexes, including tables that use the MYISAM storage engine.

The first two items also mean that, if you later add a primary key to a partitioned table that doesn't have one, the new primary key must include any columns that are used by the partitioning key. If you add a unique key to a partitioned table that has no primary key, then the same restriction applies: the new unique key must include all columns used for the partitioning expression.

The rules above apply to all types of partitioning, and not just to tables that are partitioned by RANGE.

Now we'll look at an example of creating a table that's partitioned by LIST. Earlier, we alluded to orders coming from stores that were grouped together somewhat arbitrarily into several different districts. Let's suppose that we have 19 stores in 5 districts, organized as shown here:

District #    Store #s
----------    ---------
1             1, 3, 4, 17
2             2, 12, 14
3             6, 8, 20
4             5, 7, 9, 11, 16
5             10, 13, 15, 18

We can create an orders_list table that assigns orders coming from stores in the same district to the same partition according to the scheme above, like this:

CREATE TABLE orders_list (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY LIST(store_id) (
    PARTITION p0 VALUES IN (1, 3, 4, 17),
    PARTITION p1 VALUES IN (2, 12, 14),
    PARTITION p2 VALUES IN (6, 8, 20),
    PARTITION p3 VALUES IN (5, 7, 9, 11, 16),
    PARTITION p4 VALUES IN (10, 13, 15, 18)
);

As with partitioning by RANGE, you must define each partition explicitly, but the syntax is a bit different. A partition definition for a LIST partition starts with the PARTITION keyword and a unique name for the partition, which is then followed by a VALUES IN clause and a list of one or more comma-separated integer values in parentheses. Neither the partitions nor the lists of values must be defined in any particular order, although it's probably easiest to use ascending order as much as possible, as we've done in this example. Values do not have to make up a contiguous range; you're free to skip numbers as we've done in this example - notice that there's no Store #19 in our scenario. However, no value in any of the value lists may occur in any of the others, and trying to use the same value in more than one partition results in an error.

You cannot insert a row into a table that's partitioned by LIST if the partition expression for that row yields a value that isn't in one of the value lists. There is no equivalent to RANGE partitioning's MAXVALUE for tables that are partitioned by LIST. For example, an INSERT statement, such as this one using the nonexistent store_id 19, fails:

mysql> INSERT INTO orders_list2 VALUES
    -> (1010, 2020, 19, 311, '2006-07-30', 'no such store');
ERROR 1513 (HY000): Table has no partition for value 19

Because of this, when inserting rows into a LIST-partitioned table, you should either:

Guarantee that there won't be any attempt to insert rows generating partitioning expression values that aren't listed

Be prepared to handle gracefully any errors that are raised by attempting to insert such rows.

You can use NULL in a LIST partition's value list, either by itself or amongst other values. For example, both of these are valid CREATE TABLE statements:

CREATE TABLE tn1 (c1 INT, c2 INT)
  PARTITION BY LIST(c1)  (
    PARTITION p0 VALUES IN(NULL),
    PARTITION p1 VALUES IN(0),
    PARTITION p2 VALUES IN(1, 3, 5),
    PARTITION p3 VALUES IN(2, 4)
  );

CREATE TABLE tn2 (c1 INT, c2 INT)
  PARTITION BY LIST(c1)  (
    PARTITION p0 VALUES IN(NULL, 1, 3, 5),
    PARTITION p1 VALUES IN(0, 2, 4)
  );

The statements for creating tables that are partitioned by HASH, LINEAR HASH, KEY, and LINEAR KEY are quite similar to one another. As with RANGE and LIST partitioning, you add a PARTITION BY clause to the end of the CREATE TABLE STATEMENT, followed by [LINEAR] HASH or [LINEAR] KEY and then a partitioning expression in parentheses. Unlike the case with creating a table partitioned by RANGE or LIST, you don't have to define individual partitions. Instead, you can simply use the keyword PARTITIONS followed by the desired number of partitions. This should be made clearer by looking at some examples.

The following CREATE TABLE statement creates a table that is partitioned by HASH into 4 partitions, using the value of the id column as the partitioning expression.

CREATE TABLE orders_hash (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY HASH(id)
  PARTITIONS 4;

MySQL determines which partition stores rows that are inserted into the orders_hash table using the modulus of the id value and the number of partitions (4). Internally, the partitions are numbered 0 to 3; this means that order #10203 is stored in partition #3, because 10203 mod 4 equals 3 (that is, dividing 10203 by 4 leaves a remainder of 3). More formally, the number of the partition for a table that is partitioned by HASH is calculated as

IF(ISNULL(expr), 0, ABS(expr)) MOD num

where expr is the partitioning expression and num is the number of partitions. In ordinary language, this can be expressed as follows: "If the partitioning expression value is NULL, use zero, otherwise use the absolute value of the expression; get this value modulo the number of partitions; the result is the number of the partition in which the row is stored."

If you're partitioning based on a column that contains sequential values (such as an AUTO_INCREMENT column), then HASH partitioning gives you a fairly even distribution of data amongst all partitions.

To create a table that's partitioned by LINEAR HASH, you use exactly the same syntax except that PARTITION BY HASH is replaced with PARTITION BY LINEAR HASH.

Creating a table that's partitioned by KEY or LINEAR differs from creating one that's partitioned by [LINEAR] HASH, in two respects. The first of these is that you use KEY or LINEAR KEY in the PARTITION BY clause instead of [LINEAR] HASH.

The other difference is more fundamental. Instead of using a partitioning expression that returns an integer or NULL, the expression following KEY or LINEAR KEY consists simply of a list of zero or more column names, with multiple names being separated by commas. Unlike the case with the other forms of partitioning, you can use columns of any type. Here are a couple of examples:

CREATE TABLE orders_key1 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY KEY(order_date)
  PARTITIONS 4;

CREATE TABLE orders_key2 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY KEY(salesperson_id, customer_surname)
  PARTITIONS 4;

Wait a minute - didn't we say "zero or more columns"? What happens if we remove the column names from the PARTITION BY KEY() clause in the second CREATE TABLE statement? Let's try it:

mysql> CREATE TABLE orders_key3 (
    ->   id INT AUTO_INCREMENT,
    ->   customer_surname VARCHAR(30),
    ->   store_id INT,
    ->   salesperson_id INT,
    ->   order_date DATE,
    ->   note VARCHAR(500),
    ->   INDEX idx (id)
    -> ) ENGINE = MYISAM
    ->   PARTITION BY KEY()
    ->   PARTITIONS 4;
ERROR 1475 (HY000): Field in list of fields for partition function not
found in table

This error message may seem a bit cryptic, so let us explain by way of correcting this table definition so that it works:

mysql> CREATE TABLE orders_key4 (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   customer_surname VARCHAR(30),
    ->   store_id INT,
    ->   salesperson_id INT,
    ->   order_date DATE,
    ->   note VARCHAR(500)
    -> ) ENGINE = MYISAM
    ->   PARTITION BY KEY()
    ->   PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)

See the difference? Table orders_key3 had no primary key, but orders_key4 does. If from this you draw the conclusion that not specifying a column for KEY() causes the table's primary key to be used as the partitioning key, you're correct. And as you can see here, in the event that the table has no primary key, MySQL will try to use a unique key instead:

mysql> CREATE TABLE orders_key5 (
    ->   id INT AUTO_INCREMENT,
    ->   customer_surname VARCHAR(30),
    ->   store_id INT,
    ->   salesperson_id INT,
    ->   order_date DATE,
    ->   note VARCHAR(500),
    ->   UNIQUE KEY uk (id)
    -> ) ENGINE = MYISAM
    ->   PARTITION BY KEY()
    ->   PARTITIONS 4;
Query OK, 0 rows affected (0.10 sec)

However, as we saw when we tried to create the orders_key3 table, regular indexes don't count - the table must have a primary key or unique key for this to work.

All tables using the NDBCLUSTER storage engine are automatically partitioned by KEY - in fact, MySQL Cluster supports only KEY (and LINEAR KEY) partitioning. You should also keep in mind that the Cluster storage engine creates a primary key automatically if none is defined in the CREATE TABLE statement.

Now that we've covered the basics for creating partitioned tables, let's look at some additional partition options. There are actually quite a few of these, but we're going to look at just two of them here - specifying partition index and data directories, and subpartitioning. (For additional options, see the section of the CREATE TABLE page in the MySQL Manual that covers partitioning-related syntax.)

Each PARTITION clause may specify either an INDEX DIRECTORY, a DATA DIRECTORY, or both, where the partition's indexes and/or data are to be stored. Suppose our orders database has grown to the point where it holds records relating to several million orders, tens of thousands of customers, hundreds of stores, thousands of salespeople, and so on, and it's threatening to outgrow our single 250-GB disk. We're going to upgrade our database server by adding 3 new disks, and the four disks are mounted as /data0, /data1, /data2, and /data3. We'll partition the table into 4 partitions - one per disk - and use HASH partitioning on the orders table to obtain an even distribution of data. On each disk, we'll create an orders directory and within this directory we'll create separate subdirectories for the corresponding partition's indexes and data. Using the orders_hash table described previously as a model, we can create a table that follows this scheme like this:

CREATE TABLE orders_hash2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY HASH(id) (
    PARTITION p0
      INDEX DIRECTORY = '/data0/orders/idx'
      DATA DIRECTORY = '/data0/orders/data',
    PARTITION p1
      INDEX DIRECTORY = '/data1/orders/idx'
      DATA DIRECTORY = '/data1/orders/data',
    PARTITION p2
      INDEX DIRECTORY = '/data2/orders/idx'
      DATA DIRECTORY = '/data2/orders/data',
    PARTITION p3
      INDEX DIRECTORY = '/data3/orders/idx'
      DATA DIRECTORY = '/data3/orders/data'
);

The = sign is optional. The directory path should be absolute.

Notice that there's no PARTITIONS clause; since we've defined the partitions explicitly, there's no longer any need to tell MySQL how many we want. Note that if you define any partitions for a table that's partitioned by HASH (or by KEY), you must define them all; otherwise, use PARTITIONS to specify the number of partitions. (You can use both, in which case the number used with PARTITIONS must match the number of PARTITION clauses. This can be useful as a check to insure that you've provided definitions for the correct number of partitions, but it's not strictly necessary.)

Of course, you can also specify data and index directories for other partitioning types. This example is based on the earlier orders_list table, but uses a slightly different directory scheme; here, we're just using a separate directory in /var for both the indexes and data for each partition:

CREATE TABLE orders_list2 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY LIST(store_id) (
    PARTITION p1
      VALUES IN (1, 3, 4, 17)
      INDEX DIRECTORY = '/var/orders/district1'
      DATA DIRECTORY  = '/var/orders/district1',
    PARTITION p2
      VALUES IN (2, 12, 14)
      INDEX DIRECTORY = '/var/orders/district2'
      DATA DIRECTORY  = '/var/orders/district2',
    PARTITION p3
      VALUES IN (6, 8, 20)
      INDEX DIRECTORY = '/var/orders/district3'
      DATA DIRECTORY  = '/var/orders/district3',
    PARTITION p4
      VALUES IN (5, 7, 9, 11, 16)
      INDEX DIRECTORY = '/var/orders/district4'
      DATA DIRECTORY  = '/var/orders/district4',
    PARTITION p5
      VALUES IN (10, 13, 15, 18)
      INDEX DIRECTORY = '/var/orders/district5'
      DATA DIRECTORY  = '/var/orders/district5'
);

By default, partitions are stored in the MySQL data directory. If you want to use the default directory for some partitions but not all, you can just omit the INDEX DIRECTORY and DATA DIRECTORY options from the corresponding PARTITION clauses. For a table that's partitioned by HASH or KEY, you can use just a partition name but no other options for that partition, like this:

CREATE TABLE orders_key6 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY KEY(order_date) (
    PARTITION p0
      DATA DIRECTORY  = '/var/orders',
    PARTITION p1,
    PARTITION p2
      DATA DIRECTORY  = '/var/orders',
    PARTITION p3
);

For table orders_key6, the data directories for partitions p0 and p2 are stored in /var/orders, and all other partition data and indexes are stored in the MySQL data directory.

Now let's look at creating tables with subpartitioning, which means using a different partitioning type to divide partitions into smaller subpartitions. In MySQL 5.1, you can subpartition tables that use either RANGE or LIST partitioning, and the subpartitions can use HASH, LINEAR HASH, KEY, or LINEAR KEY partitioning. Here's an example:

CREATE TABLE orders_range_hash (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY RANGE(id)
    SUBPARTITION BY HASH(store_id)
    SUBPARTITIONS 2  (
      PARTITION p0 VALUES LESS THAN(10000),
      PARTITION p1 VALUES LESS THAN(20000),
      PARTITION p2 VALUES LESS THAN(30000),
      PARTITION p3 VALUES LESS THAN(40000),
      PARTITION p4 VALUES LESS THAN(50000)
);

orders_range_hash is a table that's partitioned by RANGE and subpartitioned by HASH. In other words, each of the RANGE partitions is divided into HASH (sub)partitions for a total of 5 * 2 = 10 subpartitions. The subpartitioning is indicated using a SUBPARTITION BY clause which works analogously to PARTITION BY; it's followed by either [LINEAR] HASH or [LINEAR] KEY and then, in parentheses, a partitioning expression. This expression follows the rules that we've already discussed for partitioning expressions: with SUBPARTITION BY [LINEAR] HASH, it must evaluate to an integer or NULL; with SUBPARTITION BY [LINEAR] KEY, it can consist of zero or more table column names, the columns being of any type. the SUBPARTITION BY clause may be followed by a SUBPARTITIONS clause and a number indicating how many subpartitions each partition is to be divided into. SUBPARTITION BY and (if present) SUBPARTITIONS come after the table's PARTITION BY clause, but before the partition definitions.

It is also possible to define subpartitions explicitly using SUBPARTITION clauses, in which case you can omit SUBPARTITIONS. Each partition must have the same number of subpartition definitions, and all subpartitions must be defined at a minimum by name. The subpartition definitions are grouped together, separated by commas and inside parentheses, at the end of each partition definition. You may also specify INDEX DIRECTORY and DATA DIRECTORY options for each subpartition individually, like this:

CREATE TABLE orders_range_hash2 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY RANGE(id)
    SUBPARTITION BY LINEAR KEY(order_date) (
      PARTITION p0 VALUES LESS THAN(250000) (
        SUBPARTITION s0
          INDEX DIRECTORY = '/var/orders/d0'
          DATA DIRECTORY  = '/var/orders/i0',
        SUBPARTITION s1
          INDEX DIRECTORY = '/var/orders/d1'
          DATA DIRECTORY  = '/var/orders/i1'
      ),
      PARTITION p1 VALUES LESS THAN(500000) (
        SUBPARTITION s2
          INDEX DIRECTORY = '/var/orders/d2'
          DATA DIRECTORY  = '/var/orders/i2',
        SUBPARTITION s3
          INDEX DIRECTORY = '/var/orders/d3'
          DATA DIRECTORY  = '/var/orders/i3'
      ),
      PARTITION p2 VALUES LESS THAN(MAXVALUE) (
        SUBPARTITION s4
          INDEX DIRECTORY = '/var/orders/d4'
          DATA DIRECTORY  = '/var/orders/i4',
        SUBPARTITION s5
          INDEX DIRECTORY = '/var/orders/d5'
          DATA DIRECTORY  = '/var/orders/i5'
      )
);

If you want the data or indexes (or both) for all subpartitions in a given partition to be in the same directory, then you can just use the DIRECTORY options in the PARTITION clause instead. We'll leave it for you to experiment with this on your own, except to point out that you probably don't want to try something like this:

      # ...
      PARTITION p1 VALUES LESS THAN(500000)
          INDEX DIRECTORY = '/var/orders/d0'
          DATA DIRECTORY  = '/var/orders/i0' (
        SUBPARTITION s2
          INDEX DIRECTORY = '/var/orders/d1'
          DATA DIRECTORY  = '/var/orders/i1',
        SUBPARTITION s3
          INDEX DIRECTORY = '/var/orders/d2'
          DATA DIRECTORY  = '/var/orders/i2'
      ),
      # ...

It's allowed, but (in our opinion) not very useful, since no files corresponding to the partition itself, but only to its subpartitions, are actually created.

One other option you might find useful, especially when creating a table with lots of partitions (and possibly subpartitions) is a partition or subpartition comment. This is added using the COMMENT keyword as one of the options in a PARTITION or SUBPARTITION clause, followed by an optional = sign and then the text of the comment in quotation marks. Here's an example:

      # ...
      PARTITION p1 VALUES LESS THAN(500000)
        COMMENT = 'orders numbered between 250,000 and 500,000' (
        SUBPARTITION s2
          INDEX DIRECTORY = '/var/orders/d1'
          DATA DIRECTORY  = '/var/orders/i1'
          COMMENT = '3rd subpartition',
        SUBPARTITION s3
          INDEX DIRECTORY = '/var/orders/d2'
          DATA DIRECTORY  = '/var/orders/i2'
          COMMENT = '4th subpartition'
      ),
      # ...

Sooner or later, you'll need to make changes in partitioned tables that you've created. You can add partitions to or drop partitions from tables partitioned by RANGE or LIST, and you can merge or split partitions of tables partitioned by HASH or KEY. All of these can be accomplished using extensions to the ALTER TABLE statement. We'll look at adding and dropping partitions first.

Suppose you have this RANGE-partitioned table:

CREATE TABLE orders_range (
  id INT NOT NULL,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000)
);

Let's suppose that all of your orders with IDs less than 10,000 have long since been filled, and the data backed up offsite. You'd like to get rid of those records in your "live" database and free up some space. One way to do this is:

  DELETE FROM orders_range WHERE id < 10000;

However, you can also simply drop partition p0, like this:

  ALTER TABLE orders_range
    DROP PARTITION p0;

This ALTER TABLE statement completely removes the partition and all of the rows that were stored in it, as you can see by checking the output from SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE orders_range\G
*************************** 1. row ***************************
       Table: orders_range
Create Table: CREATE TABLE `orders_range` (
  `id` int(11) NOT NULL DEFAULT '0',
  `customer_surname` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (20000) ENGINE = MyISAM, PARTITION p2
VALUES LESS THAN (30000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN
(40000) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (50000) ENGINE =
MyISAM) */
1 row in set (0.03 sec)

(You can safely ignore the ENGINE clause that you see here. It's allowed syntax intended for a feature to be added in a future MySQL release, but in MySQL 5.1 all partitions must use the same storage engine.)

ALTER TABLE ... DROP PARTITION is also faster than DELETE. Your own results will vary depending on a multitude of factors, but in general you should find that an ALTER TABLE ... DROP PARTITION statement is anywhere from 50 to 80 percent faster than the equivalent DELETE.

Here are some things to consider when dropping partitions from tables that are partitioned by RANGE or LIST:

When you drop a partition, you delete all the data that was in that partition, just as if you'd run the equivalent DELETE statement. (But you're an excellent DBA who always makes and verifies backups, right?) Because of this, you must have the DROP privilege on a table to use ALTER TABLE ... DROP PARTITION on it.
ALTER ALTER ... DROP PARTITION does not return the number of rows that were dropped. If you need this information, use SELECT COUNT() or DELETE.
You can drop partitions in any order.
You can drop multiple partitions in a single statement by using the names of the partitions, separated by commas, in the DROP PARTITION clause; for example, to drop partitions p1 and p3 from the orders_range table, you would use:

      ALTER TABLE orders_range DROP PARTITION p1, p3;

The definition of the table is in fact changed, and rows that would have been inserted into the dropped partition are now handled according to the new partitioning scheme. With regard to the orders_range table, this means that a row with an id column value of 10,000 or less is now stored in partition p1. For a table that's partitioned by LIST, this means that rows that would have been placed in the dropped partition are now rejected with an error, because there's no longer a partition whose value list accommodates those rows.

You can also add new partitions to an existing table that's partitioned by RANGE or LIST using ALTER TABLE ... ADD PARTITION. Let's start with the original orders_range table again:

CREATE TABLE orders_range (
  id INT NOT NULL,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000)
);

You've noticed that the newest orders now have IDs in the 40,000s and that you've neglected to create the table with a partition that uses MAXVALUE, which means that trouble lies ahead. Here's how you can fix this situation:

  ALTER TABLE orders_range
    ADD PARTITION (
      PARTITION p5 VALUES LESS THAN(MAXVALUE)
    );

You can also add multiple partitions by using multiple PARTITION clauses following ADD PARTITION. For example, instead of using the previous statement to add one new partition to orders_range, we could have added two partitions to the table like this:

  ALTER TABLE orders_range
    ADD PARTITION (
      PARTITION p5 VALUES LESS THAN(60000),
      PARTITION p6 VALUES LESS THAN(MAXVALUE)
    );

The PARTITION clause as used in ALTER TABLE ... ADD PARTITION follows the same rules as it does when used in a CREATE TABLE statement.

For a table partitioned by RANGE, you can add partitions only to the high end. For example you *cannot* use the following statement to add a new partition to the orders_range table:

mysql> ALTER TABLE orders_range
    ->   ADD PARTITION (
    ->     PARTITION pn VALUES LESS THAN(5000)
    ->   );
ERROR 1480 (HY000): VALUES LESS THAN value must be strictly increasing
for each partition

(There is another way to accomplish the same task, as we'll see shortly.) There is no such restriction on adding partitions to a table partitioned by LIST; however, you cannot re-use any existing values in the value list of the partition to be added.

Now, what happens if you want to change the partitioning, but don't want to lose any data, or you want to add a new partition at some point other than the end? Using another partition-specific ALTER TABLE extension, we can split the first partition into two, one to handle id column values less than 5,000, and one to handle id values between 5,000 and 10,000:

  ALTER TABLE orders_range
    REORGANIZE PARTITION p0 INTO (
      PARTITION n0 VALUES LESS THAN(5000),
      PARTITION n2 VALUES LESS THAN(10000)
    );

If you run this statement on the original orders_range table, this is what SHOW CREATE TABLE displays afterwards:

mysql> SHOW CREATE TABLE orders_range\G
*************************** 1. row ***************************
       Table: orders_range
Create Table: CREATE TABLE `orders_range` (
  `id` int(11) NOT NULL,
  `customer_surname` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id)
(PARTITION n0 VALUES LESS THAN (5000) ENGINE = MyISAM, PARTITION n1
VALUES LESS THAN (10000) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN
(20000) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (30000) ENGINE =
MyISAM, PARTITION p3 VALUES LESS THAN (40000) ENGINE = MyISAM, PARTITION
p4 VALUES LESS THAN (50000) ENGINE = MyISAM) */
1 row in set (0.02 sec)

You can also use REORGANIZE PARTITION to merge RANGE or LIST partitions. Let's use this capability to reverse the change we just made, merging the new partitions n0 and n1 back into a single partition p0 that contains all rows whose id column value is less than 10,000:

mysql> ALTER TABLE orders_range
    ->   REORGANIZE PARTITION n0,n1 INTO (
    ->     PARTITION p0 VALUES LESS THAN(10000)
    ->   );
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_range\G
*************************** 1. row ***************************
       Table: orders_range
Create Table: CREATE TABLE `orders_range` (
  `id` int(11) NOT NULL,
  `customer_surname` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (10000) ENGINE = MyISAM, PARTITION p1
VALUES LESS THAN (20000) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN
(30000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (40000) ENGINE =
MyISAM, PARTITION p4 VALUES LESS THAN (50000) ENGINE = MyISAM) */
1 row in set (0.02 sec)

REORGANIZE PARTITION is very powerful. In fact, by specifying multiple partitions both before and after the INTO keyword, you can completely alter the partitioning scheme of a table with it:

mysql> ALTER TABLE orders_range
    ->   REORGANIZE PARTITION p0, p1, p2, p3, p4 INTO (
    ->     PARTITION r0 VALUES LESS THAN(25000),
    ->     PARTITION r1 VALUES LESS THAN(50000),
    ->     PARTITION r2 VALUES LESS THAN(MAXVALUE)
    ->   );
Query OK, 0 rows affected (0.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_range\G
*************************** 1. row ***************************
       Table: orders_range
Create Table: CREATE TABLE `orders_range` (
  `id` int(11) NOT NULL,
  `customer_surname` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id)
(PARTITION r0 VALUES LESS THAN (25000) ENGINE = MyISAM, PARTITION r1
VALUES LESS THAN (50000) ENGINE = MyISAM, PARTITION r2 VALUES LESS THAN
MAXVALUE ENGINE = MyISAM) */
1 row in set (0.02 sec)

For LIST-partitioned tables, REORGANIZE PARTITION works in the same way. The only major difference is that, with tables partitioned by RANGE, you can REORGANIZE adjacent partitions only; with LIST partitioning, no such restriction applies. The one thing you can't change with REORGANIZE PARTITION for either RANGE or LIST partitioning is the partitioning type - we'll show you how to do that at the end of this section.

PARTITION clauses used with REORGANIZE PARTITION have the same syntax and options as those used with CREATE TABLE and ADD PARTITION.

To add partitions to a table that's partitioned by HASH or KEY, you can use a slightly different form of ALTER TABLE ... ADD PARTITION. Here's an example using the orders_key1 table, which was defined like this:

CREATE TABLE orders_key1 (
  id INT AUTO_INCREMENT,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY KEY(order_date)
  PARTITIONS 4;

To add 4 more partitions to this table, use the following statement, as shown here:

mysql> ALTER TABLE orders_key1 ADD PARTITION PARTITIONS 4;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_key1\G
*************************** 1. row ***************************
       Table: orders_key1
Create Table: CREATE TABLE `orders_key1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  KEY `idx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
  /*!50100 PARTITION BY KEY (order_date) PARTITIONS 8  */
1 row in set (0.02 sec)

When used with ADD PARTITION, the number following the PARTITIONS clause is the number of partitions to be added to the table. Recall that PARTITIONS can be used only with tables partitioned by [LINEAR] HASH or [LINEAR] KEY.

Now let's reduce the number of partitions from 8 to 6. To do this, we use another ALTER TABLE extension - COALESCE PARTITION:

mysql> ALTER TABLE orders_key1 COALESCE PARTITION 2;
Query OK, 0 rows affected (1.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_key1\G
*************************** 1. row ***************************
       Table: orders_key1
Create Table: CREATE TABLE `orders_key1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  KEY `idx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
  /*!50100 PARTITION BY KEY (order_date) PARTITIONS 6  */
1 row in set (0.02 sec)

The number following the COALESCE keyword represents the number of partitions to be merged (that is, subtracted). It can be used only with tables that are partitioned by HASH, LINEAR, KEY, or LINEAR KEY.

You can completely redefine the partitioning for a table, including its partitioning type by using ALTER TABLE ... PARTITION BY. This example changes the original orders_range table so that it uses the partitioning scheme of orders_hash:

  ALTER TABLE orders_range
    PARTITION BY HASH(id)
    PARTITIONS 4;

Of course, the new partitioning scheme must be valid. For example, consider the table orders_range2, which (as we're sure you'll easily recall) was defined using this CREATE TABLE statement:

CREATE TABLE orders_range2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000),
    PARTITION p5 VALUES LESS THAN(MAXVALUE)
);

You decide to alter this table so that it uses KEY partitioning, based on the order_date column, like so:

mysql> ALTER TABLE orders_range2
    ->   PARTITION BY KEY(order_date)
    ->   PARTITIONS 8;
ERROR 1490 (HY000): A PRIMARY KEY need to include all fields in the
partition function

What happened? The orders_range2 table has a primary key on the id column, and the partitioning key must use only columns that are part of the primary key. However, since the id column uses AUTO_INCREMENT, we can't just drop the primary key. Here's one way around that:

mysql> ALTER TABLE orders_range2 ADD INDEX idx (id), DROP PRIMARY KEY;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE orders_range2
    ->   PARTITION BY KEY(order_date)
    ->   PARTITIONS 8;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_range2\G
*************************** 1. row ***************************
       Table: orders_range2
Create Table: CREATE TABLE `orders_range2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `salesperson_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `note` varchar(500) DEFAULT NULL,
  KEY `idx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY
(order_date) PARTITIONS 8  */
1 row in set (0.02 sec)

Finally, if you decide that you don't want a table to be partitioned any more, you can do this:

mysql> ALTER TABLE orders_range2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_range2\G
*************************** 1. row ***************************
       Table: orders_range2
Create Table: CREATE TABLE `orders_range2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `salesperson_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `note` varchar(500) DEFAULT NULL,
  KEY `idx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

ALTER TABLE ... REMOVE PARTITIONING does exactly what it sounds like: it turns a partitioned table into a "normal", non-partitioned table. It does not delete any data.

To perform the reverse operation - that is, to partition an existing table that isn't already partitioned - use ALTER TABLE ... PARTITION BY ...:

mysql> ALTER TABLE orders_range2
    ->   PARTITION BY RANGE(id) (
    ->     PARTITION p0 VALUES LESS THAN(50000),
    ->     PARTITION p1 VALUES LESS THAN(100000),
    ->     PARTITION p2 VALUES LESS THAN(150000),
    ->     PARTITION p3 VALUES LESS THAN(200000),
    ->     PARTITION p4 VALUES LESS THAN(MAXVALUE)
    -> );
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE orders_range2\G
*************************** 1. row ***************************
       Table: orders_range2
Create Table: CREATE TABLE `orders_range2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `salesperson_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `note` varchar(500) DEFAULT NULL,
  KEY `idx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (50000) ENGINE = MyISAM, PARTITION p1
VALUES LESS THAN (100000) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN
(150000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (200000) ENGINE
= MyISAM, PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.03 sec)

You've now seen all of the statements and the most important options that can be used to create and modify partitioned tables. Before we move on, we'll offer a few observations about performance:

Creating partitioned tables is slightly slower than creating non-partitioned tables.
ALTER TABLE ... DROP PARTITION is usually quite a bit faster than DELETE.
ALTER TABLE ... ADD PARTITION is fairly quick on tables partitioned by RANGE and LIST, because there's no data to be moved into the new partitions.
When ALTER TABLE ... ADD PARTITION is used on a table partitioned by KEY or HASH, this depends on how many rows are already stored in the table - the more data, the longer it will take to add the new partitions. Using LINEAR HASH or LINEAR KEY partitioning when creating the table will make such operations faster.
ALTER TABLE ... COALESCE PARTITION, ALTER TABLE ... REORGANIZE PARTITION, and ALTER TABLE ... PARTITION BY can be quite slow when run on tables containing ten or hundreds of thousands (or more) rows. As with ADD PARTITION, LINEAR HASH and LINEAR KEY partitioning will make COALESCE operations a bit faster. ALTER TABLE ... REMOVE PARTITIONING is somewhat faster than these, as MySQL doesn't have to determine which file a row needs to be placed in, even though it must still move the data.

In general, you should avoid gratuitous schema modifications of partitioned tables once you've started using them to store data, the exceptions being DROP PARTITION and ADD PARTITION with tables that are partitioned by RANGE or LIST.

The two big advantages

PRUNING.

Speed depends on a lot of factors, but one of them is inevitably the table's row-count. Whether you're doing a sequential scan or an indexed lookup, either way it takes longer - maybe a lot, maybe a little - if there are 2 million rows instead of 1 million.

But what if there were two one-million-row partitions, and the DBMS somehow "knew" that it only had to scan or lookup in one partition? Why, then it would be searching a 2-million-row table as if it was a 1-million-row table. And there is a class of searches where the DBMS can know exactly that. Some examples:

CREATE TABLE t (column1 INT) PARTITION BY RANGE (s1)
(PARTITION p1 VALUES LESS THAN (500000),
 PARTITION p2 VALUES LESS THAN (800000),
 PARTITION p2 VALUES LESS THAN MAXVALUE);

SELECT * FROM t WHERE column1 = 999999; /* Use p3 */
SELECT * FROM t WHERE column1 < 5000;   /* Use p1 */
SELECT * FROM t WHERE column1 BETWEEN 400000 AND 600000);
/* Use p1 + p2 but not p3 */

MySQL can find what it needs to know during the optimizing stage - before any table or index accesses - so the advantage will be greater as the granularity of the partitioning gets smaller.

This find-out-at-optimize-time trick is called PRUNING.

Pruning is good with RANGE and LIST partitions, irrelevant with HASH and KEY partitions.

Pruning is applicable when the search condition is the same as or narrower than the partitioning condition, inapplicable otherwise.

Pruning doesn't merely save speed, it saves locks.

BIG-TABLE MAINTENANCE.

Big tables mean big files. Suppose you have 4GB in your table and you worry that it will be hard to move it to another computer system which has trouble with files that size. No problem: partition it into two 2GB partitions.

Big tables mean slow maintenance. Suppose you have a big update or reorganization, but you can only do it while everybody else is on lunch break. No problem: if it's partitioned, you can do the first part during Monday lunch, and the second part during Tuesday lunch.

Doing It With Dates And Times

Partitioning tables according to date and time values is a natural thing to want to do. For example, you might want to segregate orders by year, quarter or month. Some database management systems provide special syntax for partitioning tables and working with partitioned tables using dates and times. MySQL doesn't do this, but that doesn't keep you from using partitioning schemes that are based on dates and times.

Most likely you'll want to use RANGE or LIST partitioning when working with dates and times, since HASH and KEY don't really allow you to choose which values go into which partitions. With RANGE and LIST partitioning, the partitioning expression must produce an integer value or NULL; if you try to create one of these types of partitioned table using a DATE, TIME, or DATETIME column, the CREATE TABLE statement will fail with an error:

mysql> CREATE TABLE d1 (c1 DATE) PARTITION BY RANGE(c1)(
    ->   PARTITION p0 VALUES LESS THAN('1999-12-31'),
    ->   PARTITION p1 VALUES LESS THAN('2005-12-31'),
    ->   PARTITION p2 VALUES LESS THAN(MAXVALUE)
    -> );
ERROR 1064 (42000): VALUES value must be of same type as partition
function near '),
  PARTITION p1 VALUES LESS THAN('2005-12-31'),
  PARTITION p2 VALUES LESS THA' at line 2

(Of course, the same is true of tables partitioned by HASH.)

This happens because column c1 holds values that aren't integers or NULL (and because we tried to compare with such values in the partition definitions). To get round this problem, you can use a function that converts a date to an integer, like this:

mysql> CREATE TABLE d1 (c1 DATE) PARTITION BY RANGE( YEAR(c1) ) (
    ->   PARTITION p0 VALUES LESS THAN(2000),
    ->   PARTITION p1 VALUES LESS THAN(2005),
    ->   PARTITION p2 VALUES LESS THAN(MAXVALUE)
    -> );
Query OK, 0 rows affected (0.92 sec)

Table d1 uses the YEAR() function to obtain the year as an integer from the DATE column value, then uses this in partitioning the table so that records from before the year 2000 go into partition p0, records from 2000 through the end of 2005 are stored in partition p1, and records from 2006 and later go into partition p2.

The functions TO_DAYS() and YEAR() are particularly well-suited for use with partitioning (MySQL's optimizer "knows" how to work well with them). However, they're not the only functions available for use in partitioning expressions. The following table shows what we think are likely to be the most useful of the built-in MySQL functions that act on a DATE, TIME, or DATETIME value and return an integer.

MySQL Date and Time Functions that Return an Integer
-------------------------------------------------------
DAY()       LAST_DAY()    SECOND()         WEEK()
DAYOFWEEK() MICROSECOND() TIME_TO_SEC()    WEEKDAY()
DAYOFYEAR() MINUTE()      TIMESTAMPDIFF()  WEEKOFYEAR()
EXTRACT()   MONTH()       TO_DAYS()        YEAR()
HOUR()      QUARTER()     UNIX_TIMESTAMP() YEARWEEK()
-------------------------------------------------------

(For complete information regarding these functions, see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html.)

Here's an example. Suppose you'd like to partition the orders table so that there's a separate partition for each quarter of the year. One way to accomplish this is to create a table partitioned by LIST, using the QUARTER() function in the partitioning expression, as shown here:

CREATE TABLE orders_quarters_1 (
  id INT AUTO_INCREMENT NOT NULL,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY LIST( QUARTER(order_date) ) (
    PARTITION p0 VALUES IN(1),
    PARTITION p1 VALUES IN(2),
    PARTITION p2 VALUES IN(3),
    PARTITION p3 VALUES IN(4)
);

If we want to partition based on the year and the quarter, we can do something a bit more elaborate, like this:

CREATE TABLE orders_yq_1 (
  id INT AUTO_INCREMENT NOT NULL,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY LIST(YEAR(order_date) * 100 + QUARTER(order_date)) (
    PARTITION y2005q3 VALUES IN(200503),
    PARTITION y2005q4 VALUES IN(200504),
    PARTITION y2006q1 VALUES IN(200601),
    PARTITION y2006q2 VALUES IN(200602),
    PARTITION y2006q3 VALUES IN(200603),
    PARTITION y2006q4 VALUES IN(200604),
    PARTITION y2007q1 VALUES IN(200701)
);

One nice thing about partitioning tables based on dates is that you can add and drop partitions with the passage of time. Suppose you're interested in keeping data for only the current quarter plus the three last and three upcoming quarters. When a new quarter begins, you can drop the partition for the earliest quarter, and add another partition for a new quarter. This is also known as rolling in and rolling out partitions.

This looks pretty useful and dropping the partition is likely to be much faster than a DELETE statement. Unfortunately, SELECTs from this table aren't going to be terribly efficient:

mysql> EXPLAIN PARTITIONS SELECT * FROM orders_yq_1
    -> WHERE order_date BETWEEN '2006-03-01' AND '2006-09-30'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_years_quarters_1
   partitions: y2005q3,y2005q4,y2006q1,y2006q2,y2006q3,y2006q4,y2007q1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 120
        Extra: Using where
1 row in set (0.05 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM orders_years_quarters_1
    ->   WHERE YEAR(order_date) = 2006
    ->   AND QUARTER(order_date) BETWEEN 2 AND 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_years_quarters_1
   partitions: y2005q3,y2005q4,y2006q1,y2006q2,y2006q3,y2006q4,y2007q1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 120
        Extra: Using where
1 row in set (0.02 sec)

Of course, we can improve matters somewhat by adding an index on the order_date column:

mysql> ALTER TABLE orders_years_quarters_1 ADD INDEX d (order_date);
Query OK, 120 rows affected (0.35 sec)
Records: 120  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM orders_yq_1
    ->   WHERE order_date BETWEEN '2006-07-15' AND '2006-08-21'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_yq_1
   partitions: y2005q3,y2005q4,y2006q1,y2006q2,y2006q3,y2006q4,y2007q1
         type: range
possible_keys: d
          key: d
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using where
1 row in set (0.05 sec)

A range scan is about as good as we can expect from this. And we're not getting the benefit of any partition pruning - all partitions are being scanned. The solution is to use TO_DAYS(). We can also simplify the partitioning expression and use RANGE partitioning as well:

CREATE TABLE orders_yq_2 (
  id INT AUTO_INCREMENT NOT NULL,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500),
  INDEX idx (id)
) ENGINE = MYISAM
  PARTITION BY RANGE( TO_DAYS(order_date) ) (
    PARTITION y2005q3 VALUES LESS THAN( TO_DAYS('2005-10-01') ),
    PARTITION y2005q4 VALUES LESS THAN( TO_DAYS('2006-01-01') ),
    PARTITION y2006q1 VALUES LESS THAN( TO_DAYS('2006-04-01') ),
    PARTITION y2006q2 VALUES LESS THAN( TO_DAYS('2006-07-01') ),
    PARTITION y2006q3 VALUES LESS THAN( TO_DAYS('2006-10-01') ),
    PARTITION y2006q4 VALUES LESS THAN( TO_DAYS('2007-01-01') ),
    PARTITION y2007q1 VALUES LESS THAN( TO_DAYS('2007-04-01') )
);

(Yes, you can use a function or other expression in a VALUES LESS THAN clause, just as long as it evaluates to an integer value or NULL. The same is true with regard to VALUES IN.)

Now let's try EXPLAIN PARTITIONS on some queries against this table, and see if we notice any improvement.

mysql> EXPLAIN PARTITIONS SELECT * FROM orders_yq_2
    ->   WHERE YEAR(order_date) = 2006
    ->   AND QUARTER(order_date) BETWEEN 2 AND 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_yq_2
   partitions: y2005q3,y2005q4,y2006q1,y2006q2,y2006q3,y2006q4,y2007q1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 120
        Extra: Using where
1 row in set (0.01 sec)

We're still scanning all partitions, which isn't good. In order to take advantage of pruning, we're going to have to couch the SELECT in terms of dates, without any intravening function calls:

mysql> EXPLAIN PARTITIONS SELECT * FROM orders_yq_2
    ->   WHERE order_date BETWEEN '2006-04-01' AND '2006-09-30'
    ->   ORDER BY order_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders_yq_2
   partitions: y2006q2,y2006q3
         type: ALL
possible_keys: d
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 30
        Extra: Using where; Using filesort
1 row in set (0.01 sec)

Now only the 2 partitions where it's possible to find matching records are being scanned. However, the index isn't being used. Let's see how the speed of a similar query (using COUNT(*) to save space here) compares when run on the two tables.

mysql> SELECT COUNT(*) FROM orders_years_quarters_1
    ->   WHERE order_date BETWEEN '2006-07-01' AND '2006-09-30'
    ->   ORDER BY order_date\G
*************************** 1. row ***************************
COUNT(*): 30
1 row in set (0.05 sec)

mysql> SELECT COUNT(*) FROM orders_yq_2
    ->   WHERE order_date BETWEEN '2006-07-15' AND '2006-08-21'
    ->   ORDER BY order_date\G
*************************** 1. row ***************************
COUNT(*): 30
1 row in set (0.01 sec)

Even without the use of indexes, partition pruning can still save lots of time.

Of course, any instance where you can use integers rather than actual dates, times, or datetimes - in YYYYMMDD, HHMMSS, and YYYYDDMMHHMMSS format, respectively - will allow you to take advantage of pruning just as you can with any other integer columns.

In the future, additional date functions may be optimized for use with partitioning. UNIX_TIMESTAMP() and YEARWEEK() are good candidates, since they're both generally increasing functions. We also plan to make some enhancements to RANGE partitioning in a future releases that might help when working with dates and times.

Some simple speed tests

MySQL has a department which runs all the most famous and sophisticated DBMS benchmark tests, so you can be assured that there is not going to be any general performance problem. But, after all, all mileages vary. So here's a very simple way to get a rough idea of what effect partitioning will have on a table in your database.

A "typical" table should have a primary key, and we're partial to using integers for partitioning, so for our example we'll create a table whose primary key and partitioning are both based on "column1 INT". We'll populate this table with one million rows.

Initially there are no partitions.

DELIMITER //
CREATE TABLE t (column1 INT PRIMARY KEY, column2 INT) ENGINE=INNODB//
CREATE PROCEDURE p1 ()
BEGIN
  DECLARE v INT DEFAULT 1;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  WHILE v <= 1000000 DO
    INSERT INTO t VALUES (v,v);
    SET v = v + 1;
    END WHILE;
  END//
CALL p1()//

Get the average time to select one row via the PRIMARY key.

CREATE PROCEDURE p2 ()
BEGIN
  DECLARE v INT DEFAULT 1;
  DECLARE start_time, end_time TIMESTAMP;
  SET start_time = CURRENT_TIMESTAMP;
  WHILE v <= 10000 DO
    SELECT column2 INTO @v FROM t WHERE column1 = v;
    SET v = v + 1;
    END WHILE;
  SET end_time = CURRENT_TIMESTAMP;
  SELECT "SELECT average time = ",(end_time - start_time) / 10000;
  END//
CALL p2()//

Get the average time to select one row via the NON-PRIMARY column.

CREATE PROCEDURE p3 ()
BEGIN
  DECLARE v INT DEFAULT 1;
  DECLARE start_time, end_time TIMESTAMP;
  SET start_time = CURRENT_TIMESTAMP;
  WHILE v <= 100 DO
    SELECT column2 INTO @v FROM t WHERE column2 = v;
    SET v = v + 1;
    END WHILE;
  SET end_time = CURRENT_TIMESTAMP;
  SELECT "SELECT average time = ",(end_time - start_time) / 100;
  END//
CALL p3()//

Now find out what the average primary-key value is.

SELECT AVG(column1) FROM t//

As it happens, the average is approximately 500000.

Now partition the table so that half the rows (all those less than the average are in partition p1, and the rest are in partition p2.

ALTER TABLE t
PARTITION BY RANGE (column1)
(PARTITION p1 VALUES LESS THAN (500000),
 PARTITION p10 VALUES LESS THAN MAXVALUE)//

Now, once again,
Get the average time to select one row via the PRIMARY.

CALL p2()//

Now, once again,
Get the average time to select one row via the NON-PRIMARY.

CALL p3()//

And here's what we found:

Test         Average Time             Average Time
             Without Partitioning     With Partitioning

PRIMARY          0.0006               0.0006
NON-PRIMARY      1.85                 2.01

The key, of course, is that the primary key is the partitioned key. So we see that, if we partition and all the rows we're operating on are in the same partition, things don't get slower. Otherwise, if we partition and the rows could be in any or all partitions, things get slower. That's what we expect.

There are all sorts of real-life complications! So do it yourself, with your own computer and your own table (if you happen to have a big table that has a primary key). Obviously the more partitions, the greater the difference. An interesting variation is to put each partition on a different disk drive.

TIP: As with all situations where "it goes faster when I do this but slower when I do that", you can consider replicating the database and making the replica with different parameters. Then you can, say, direct one type of query to the replica where partitioning is in, and the other type of query to the replica where partitioning is out.

Other DBMSs

Since partitioning is a good thing, it's not surprising that other DBMSs have some support for it. This overview is mainly for people who want to know how to migrate to MySQL, but it's a good opportunity for a vague sort of feature comparison.

An example table:

MySQL               DB2 9                 Oracle10g         SQL Server 2005

CREATE TABLE        CREATE TABLE          CREATE TABLE      CREATE TABLE
table_name          table_name            table_name        table_name
(column_names)      (column_names)        (column_names)    (column_names)
PARTITION BY        PARTITION BY          PARTITION BY      ON
RANGE               RANGE                 RANGE             partition_scheme_name
(column_name)       (column_name)         (column_name(s))  [(partition_column_name)]
(                   (                     (
partition_name      [partition_name]      [partition_name]
VALUES LESS THAN    STARTING              VALUES LESS THAN
(literal)           literal               (literal)
[,...]              [,...]                [,...]
                    [ENDING literal]
                    [EVERY n increments]
)                   )                     )

An explanation of the example table:

MySQL and DB2 9 and Oracle10g have nearly-alike CREATE TABLE partitioning clauses; SQL Server 2005 is different because one must define a "partition scheme" with a separate statement. Although the ANSI/ISO SQL:2003 standard has no specification for partitions, it's clear from the example table that there is a de facto standard which most major vendors very vaguely adhere to.

With Oracle10g there can be more than one column in the range partition; with MySQL there's only one.

With DB2 9 and Oracle10g the partition_name is optional, with MySQL it's compulsory.

With DB2 9 one can specify the ends of ranges, with MySQL one can only specify the starts (so there are no gaps between ranges).

With DB2 9 there is an additional clause for making partitions automatically according to a formula like "EVERY 3 MONTHS", with MySQL one would have to do so with a stored procedure, as we'll show in one of our examples.

The DB2 9 document only mentions RANGE partitions, so we that's all we show in the example table. Actually both MySQL and Oracle10g have many other things (other partition types, subpartitions) which all have approximately the same syntax.

All DBMSs vendors have further statements for altering and maintaining partitions, which we needn't describe here.

Here is another table with a features checklist:

Feature	MySQL	DB2 9	Oracle10g	SQL Server 2005
Basic Partitioning	YES	YES	YES	YES
"Standard" syntax	YES	YES	YES	NO
RANGE	YES	YES	YES	YES
Other than RANGE	YES	[Note1]	YES	YES
Most Data Types	NO		YES	YES
Subpartitioning	YES	NO	YES	NO
Pruning	YES	[Note1]	YES	YES
Parallelism [Note2]	NO	[Note1]	YES	[Note1]

[Note1] We did not find the feature in the vendor manual, and/or could not test at time of writing.

[Note2] The idea of "parallelism" is that one scan partition #1 with one thread, and at the same time scan partition #2 with another thread.

From the example table and the features checklist in this section, it should be clear that MySQL isn't trying to dazzle or revolutionize. MySQL is ensuring that users have migration possibilities and ensuring that users have essentially the same power as with all other top-tier DBMSs.

Example: Making a 500-partition table

Efficient Ed has to create a table with 500 partitions. To eliminate the tedium of typing in 500 "PARTITION partition_name VALUES IN (x)" clauses, he writes a stored procedure. This takes advantage of the dynamic SQL which is possible with the relatively new "prepared statements" feature.

DELIMITER //
CREATE TABLE t (column1 INT)
  PARTITION BY LIST (column1)
  (PARTITION pn VALUES IN (NULL));
CREATE PROCEDURE p ()
BEGIN
  DECLARE v INT DEFAULT 1;
  WHILE v <= 500 DO
    SET @stmt1 = CONCAT('ALTER TABLE t',
                 'ADD PARTITION p',v,
                 VALUES IN (',v,')');
    PREPARE stmt1 FROM @stmt1;
    EXECUTE stmt1;
    SET v = v + 1;
    END WHILE;
  END//
CALL p()//

Explanations:

Ed starts by making a table with one partition. Then he writes a procedure which has a loop which repeats 500 times. Inside the loop he has a SET statement which makes a string like "ALTER TABLE t ADD PARTITION p# VALUES IN (#))" where # is 1n integer between 1 and 500. Then come the PREPARE and EXECUTE statements which are the dynamic SQL we spoke of. After calling this procedure, Ed has a table with 501 partitions. The maximum number of partitions per table is X.

Example: Patching Maintenance Mistakes

Clumsy Clarence does things to tables that he later regrets.

He starts with a table that's partitioned by range:

CREATE TABLE t (column1 TINYINT UNSIGNED)
PARTITION BY RANGE (column1)
(PARTITION p1 VALUES LESS THAN (1),
 PARTITION p2 VALUES LESS THAN (2),
 PARTITION p3 VALUES LESS THAN (255));

He drops a RANGE partition and then tries to put it back:

ALTER TABLE t DROP PARTITION p2;
ALTER TABLE t ADD PARTITION (PARTITION p2 VALUES LESS THAN (2));
Oops, he gets an error message:
"ERROR 1480 (HY000): VALUES LESS THAN value must be strictly increasing
for each partition"

But there's another statement that would do what he wants:

ALTER TABLE t
 REORGANIZE PARTITION p3 INTO
 (PARTITION p2 VALUES LESS THAN (2),
  PARTITION p3 VALUES LESS THAN MAXVALUE);

Now, after searching for "WHERE (column1 <> 1 AND column1 <> 2)" and getting zero hits, he decides that he might as well use list partitioning instead:

ALTER TABLE t
PARTITION BY LIST (column1)
(PARTITION p1 VALUES IN (1),
 PARTITION p2 VALUES IN (2));

And he gets another error message:

ERROR 1513 (HY000): Table has no partition for value NULL

Alas, poor Clarence forgot that searches for "<>" won't match on NULLs, but LESS THAN conditions will - NULL is a value which is "less than (1)" for partitioning. So he he should add "PARTITION pn VALUES IN (NULL)". Or he should give up and turn t into a non-partitioned table:

ALTER TABLE t REMOVE PARTITIONING;

Example: Searching One Partition at a time

With MyISAM tables, each statement causes a lock. Dora The DBA discovers that some "maintenance update" statements harm concurrency with a huge table. So she arranges that the statements will only process 1,000,000 rows at a time, breaking up by partition. This takes advantage of the pruning feature.

Her procedure looks like this.

CREATE PROCEDURE p ()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE v_partition_name VARCHAR(64) CHARACTER SET utf8;
  DECLARE v_partition_method VARCHAR(12);
  DECLARE v_partition_expression LONGTEXT;
  DECLARE v_partition_description LONGTEXT;
  DECLARE v_table_rows BIGINT;
  DECLARE c CURSOR FOR
  SELECT partition_name, partition_method, partition_expression,
  partition_description, table_rows
  FROM information_schema.partitions
  WHERE table_name = 'my_big_table' AND table_schema = 'my_database';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;

  x: LOOP

    FETCH c INTO v_partition_name, v_partition_method,
    v_partition_expression, v_partition_description, v_table_rows;

    /*
      The 'done' flag will become TRUE when the NOT FOUND handler
      comes into play, meaning there are no more rows to fetch.
    */
    IF done THEN
      LEAVE x;
      END IF;

    /*
      The assumption is that partitioning is by LIST.
      If not, do nothing.
    */
    IF v_partition_method <> 'LIST' OR v_partition_method IS NULL THEN
      SELECT 'ERROR: The table does not have LIST partitioning.';
      LEAVE x;
      END IF;

    /*
      The table_rows column in information_schema.partitions is a
      rough guide to the size of a partition, just as the table_rows
      column in information information_schema.tables is a rough
      guide to the size of a table, for MyISAM. If a partition is
      bigger than the arbitrary million-row limit, Dora will reorganize.
    */
    IF v_table_rows > 1000000 THEN
      SELECT 'WARNING: partition with more than 1 million rows';
      END IF;

    /*
      The original "maintenance update" statement looked like:
      UPDATE my_big_table SET balance=balance+1;
      (This is a fiction story; if the update was really so simple
      then we wouldn't go to any trouble over it.)
      The procedure makes new maintenance update statements like:
      UPDATE my_big_table SET balance=balance+1
      WHERE partition_column = partition_value;
    */
    IF v_partition_description = 'NULL' THEN
      SET @v = CONCAT('UPDATE my_big_table SET balance = balance + 1',
                      ' WHERE ', v_partition_expression,
                      ' IS ', v_partition_description);
    ELSE
      SET @v = CONCAT('UPDATE my_big_table SET balance = balance + 1',
                      ' WHERE ', v_partition_expression,
                      ' = ', v_partition_description);
    END IF;

    PREPARE stmt1 FROM @v;
    EXECUTE stmt1;

    END LOOP;

  CLOSE c;
  END//

References

MySQL Reference Manual
"Chapter 18. Partitioning"
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

[DB2 9 partitioning]
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0605ahuja2/index.html

[Oracle partitioning]
"16 Managing Partitioned Tables and Indexes"
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm

[SQL Server partitioning]
"CREATE PARTITION FUNCTION (Transact-SQL)"
http://msdn2.microsoft.com/en-us/library/ms187802.aspx

"Improving Database Performance with Partitioning"
http://dev.mysql.com/tech-resources/articles/performance-partitioning.html

"More on MySQL 5.1 Partitioning"
http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html

"MYSQL Partitioning forum"
http://forums.mysql.com/list.php?106

Another place to find partition-using example statements is in the mysql-test directory which comes with the source code.

Bye

This was the second opus in the "MySQL New Features" features. If you liked it, you'll like the last one too:

"MySQL 5.1 New Features: Events"
http://dev.mysql.com/tech-resources/articles/mysql-events.html