PARTITIONS table is a
nonstandard table. It was added in MySQL 5.1.6.
Each record in this table corresponds to an individual partition or subpartition of a partitioned table.
TABLE_CATALOG: This column is always
TABLE_SCHEMA: This column contains the name
of the database to which the table belongs.
TABLE_NAME: This column contains the name
of the table containing the partition.
PARTITION_NAME: The name of the partition.
SUBPARTITION_NAME: If the
PARTITIONS table record
represents a subpartition, then this column contains the name
of subpartition; otherwise it is
PARTITION_ORDINAL_POSITION: All partitions
are indexed in the same order as they are defined, with
1 being the number assigned to the first
partition. The indexing can change as partitions are added,
dropped, and reorganized; the number shown is this column
reflects the current order, taking into account any indexing
Subpartitions within a given partition are also indexed and
reindexed in the same manner as partitions are indexed within
PARTITION_METHOD: One of the values
LINEAR KEY; that
is, one of the available partitioning types as discussed in
Section 18.2, “Partition Types”.
SUBPARTITION_METHOD: One of the values
LINEAR KEY; that
is, one of the available subpartitioning types as discussed in
Section 18.2.5, “Subpartitioning”.
For example, consider a partitioned table created in the
test database using this statement:
CREATE TABLE tp ( c1 INT, c2 INT, c3 VARCHAR(25) ) PARTITION BY HASH(c1 + c2) PARTITIONS 4;
PARTITION_EXPRESSION column in a
PARTITIONS table record for a partition from this table
c1 + c2, as shown here:
SELECT DISTINCT PARTITION_EXPRESSION>
WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';+----------------------+ | PARTITION_EXPRESSION | +----------------------+ | c1 + c2 | +----------------------+ 1 row in set (0.09 sec)
SUBPARTITION_EXPRESSION: This works in the
same fashion for the subpartitioning expression that defines
the subpartitioning for a table as
PARTITION_EXPRESSION does for the
partitioning expression used to define a table's partitioning.
If the table has no subpartitions, then this column is
PARTITION_DESCRIPTION: This column is used
for RANGE and LIST partitions. For a
partition, it contains the value set in the partition's
VALUES LESS THAN clause, which can be
either an integer or
MAXVALUE. For a
LIST partition, this column contains the
values defined in the partition's
clause, which is a comma-separated list of integer values.
For partitions whose
LIST, this column is always
TABLE_ROWS: The number of table rows in the
the row count given in the
column is only an estimated value used in SQL optimization,
and may not always be exact.
AVG_ROW_LENGTH: The average length of the
rows stored in this partition or subpartition, in bytes.
This is the same as
DATA_LENGTH divided by
DATA_LENGTH: The total length of all rows
stored in this partition or subpartition, in bytes —
that is, the total number of bytes stored in the partition or
MAX_DATA_LENGTH: The maximum number of
bytes that can be stored in this partition or subpartition.
INDEX_LENGTH: The length of the index file
for this partition or subpartition, in bytes.
DATA_FREE: The number of bytes allocated to
the partition or subpartition but not used.
CREATE_TIME: The time of the partition's or
UPDATE_TIME: The time that the partition or
subpartition was last modified.
CHECK_TIME: The last time that the table to
which this partition or subpartition belongs was checked.
Some storage engines do not update this time; for tables
using these storage engines, this value is always
CHECKSUM: The checksum value, if any;
otherwise, this column is
PARTITION_COMMENT: This column contains the
text of any comment made for the partition.
The default value for this column is an empty string.
NODEGROUP: This is the nodegroup to which
the partition belongs. This is relevant only to MySQL Cluster
tables; otherwise the value of this column is always
TABLESPACE_NAME: This column contains the
name of tablespace to which the partition belongs. In MySQL
5.1, the value of this column is always
If any partitioned tables created in a MySQL version prior
to MySQL 5.1.6 are present following an upgrade to MySQL
5.1.6 or later, it is not possible to
PARTITIONS table. See
Section C.1.46, “Changes in MySQL 5.1.6 (01 February 2006)” before
upgrading from MySQL 5.1.5 or earlier to MySQL 5.1.6 or
A nonpartitioned table has one record in
however, the values of the
PARTITION_DESCRIPTION columns are all
PARTITION_COMMENT column in this case is
In MySQL 5.1, there is also only one record in
PARTITIONS table for a table
engine. The same columns are also
empty) as for a nonpartitioned table.