Table ts has 3 RANGE
partitions. Each of these
partitions—p0, p1,
and p2—is further divided into 2
subpartitions. In effect, the entire table is divided into
3 * 2 = 6 partitions. However, due to the
action of the PARTITION BY RANGE clause, the
first 2 of these store only those records with a value less than
1990 in the purchased column.

In MySQL 5.7, it is possible to subpartition tables
that are partitioned by RANGE or
LIST. Subpartitions may use either
HASH or KEY partitioning.
This is also known as composite
partitioning.

Note

SUBPARTITION BY HASH and
SUBPARTITION BY KEY generally follow the
same syntax rules as PARTITION BY HASH and
PARTITION BY KEY, respectively. An
exception to this is that SUBPARTITION BY
KEY (unlike PARTITION BY KEY)
does not currently support a default column, so the column
used for this purpose must be specified, even if the table has
an explicit primary key. This is a known issue which we are
working to address; see
Issues with subpartitions, for
more information and an example.

It is also possible to define subpartitions explicitly using
SUBPARTITION clauses to specify options for
individual subpartitions. For example, a more verbose fashion of
creating the same table ts as shown in the
previous example would be:

This statement would still fail even if it included a
SUBPARTITIONS 2 clause.

Each SUBPARTITION 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.

Subpartition names must be unique across the entire table.
For example, the following CREATE
TABLE statement is valid in MySQL
5.7:

Subpartitions can be used with especially large
MyISAM tables to distribute data
and indexes across many disks. Suppose that you have 6 disks
mounted as /disk0,
/disk1, /disk2, and so
on. Now consider the following example:

Rows with purchased 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
(s0a and s0b) making
up partition p0. In other words:

The data for subpartition s0a is
stored on /disk0.

The indexes for subpartition s0a are
stored on /disk1.

The data for subpartition s0b is
stored on /disk2.

The indexes for subpartition s0b are
stored on /disk3.

Rows containing dates ranging from 1990 to 1999 (partition
p1) do not require as much room as those
from before 1990. These are split between 2 disks
(/disk4 and
/disk5) rather than 4 disks as with the
legacy records stored in p0:

Data and indexes belonging to p1's
first subpartition (s1a) are stored
on /disk4—the data in
/disk4/data, and the indexes in
/disk4/idx.

Data and indexes belonging to p1's
second subpartition (s1b) are stored
on /disk5—the data in
/disk5/data, and the indexes in
/disk5/idx.

Rows reflecting dates from the year 2000 to the present
(partition p2) 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.

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 ALTER
TABLE ... REORGANIZE PARTITION statement. See
Chapter 4, Partition Management, for an
explanation of how this can be done.

The DATA DIRECTORY and INDEX
DIRECTORY options are not permitted in partition
definitions when the
NO_DIR_IN_CREATE server SQL
mode is in effect. In MySQL 5.7, these options are
also not permitted when defining subpartitions (Bug #42954).