24.16 The INFORMATION_SCHEMA PARTITIONS Table
PARTITIONS table provides information about table partitions. Each row in this table corresponds to an individual partition or subpartition of a partitioned table. For more information about partitioning tables, see Chapter 22, Partitioning.
PARTITIONS table has these columns:
The name of the catalog to which the table belongs. This value is always
The name of the schema (database) to which the table belongs.
The name of the table containing the partition.
The name of the partition.
PARTITIONStable row represents a subpartition, the name of subpartition; otherwise
All partitions are indexed in the same order as they are defined, with
1being 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 changes.
Subpartitions within a given partition are also indexed and reindexed in the same manner as partitions are indexed within a table.
One of the values
LINEAR KEY; that is, one of the available partitioning types as discussed in Section 22.2, “Partitioning Types”.
One of the values
LINEAR KEY; that is, one of the available subpartitioning types as discussed in Section 22.2.6, “Subpartitioning”.
For example, consider a partitioned table created in the
testdatabase using this statement:
CREATE TABLE tp ( c1 INT, c2 INT, c3 VARCHAR(25) ) PARTITION BY HASH(c1 + c2) PARTITIONS 4;
PARTITION_EXPRESSIONcolumn in a
PARTITIONStable row for a partition from this table displays
c1 + c2, as shown here:
mysql> SELECT DISTINCT PARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test'; +----------------------+ | PARTITION_EXPRESSION | +----------------------+ | c1 + c2 | +----------------------+
This works in the same fashion for the subpartitioning expression that defines the subpartitioning for a table as
PARTITION_EXPRESSIONdoes for the partitioning expression used to define a table's partitioning.
If the table has no subpartitions, this column is
This column is used for RANGE and LIST partitions. For a
RANGEpartition, it contains the value set in the partition's
VALUES LESS THANclause, which can be either an integer or
MAXVALUE. For a
LISTpartition, this column contains the values defined in the partition's
VALUES INclause, which is a list of comma-separated integer values.
For partitions whose
PARTITION_METHODis other than
LIST, this column is always
The number of table rows in the partition.
InnoDBtables, the row count given in the
TABLE_ROWScolumn is only an estimated value used in SQL optimization, and may not always be exact.
The average length of the rows stored in this partition or subpartition, in bytes. This is the same as
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 subpartition.
The maximum number of bytes that can be stored in this partition or subpartition.
The length of the index file for this partition or subpartition, in bytes.
For partitions of
NDBtables, whether the tables use implicit or explicit partitioning, the
INDEX_LENGTHcolumn value is always 0. However, you can obtain equivalent information using the ndb_desc utility.
The number of bytes allocated to the partition or subpartition but not used.
The time that the partition or subpartition was created.
The time that the partition or subpartition was last modified.
The last time that the table to which this partition or subpartition belongs was checked.
InnoDBtables, the value is always
The checksum value, if any; otherwise
The text of the comment, if the partition has one. If not, this value is empty.
The maximum length for a partition comment is defined as 1024 characters, and the display width of the
PARTITION_COMMENTcolumn is also 1024, characters to match this limit.
This is the nodegroup to which the partition belongs. This is relevant only to NDB Cluster tables; otherwise, the value is always
The name of the tablespace to which the partition belongs. The value is always
DEFAULT, unless the table uses the
NDBstorage engine (see the Notes at the end of this section).
PARTITIONSis a nonstandard
A table using any storage engine other than
NDBand which is not partitioned has one row in the
PARTITIONStable. However, the values of the
PARTITION_DESCRIPTIONcolumns are all
NULL. Also, the
PARTITION_COMMENTcolumn in this case is blank.
NDBtable which is not explicitly partitioned has one row in the
PARTITIONStable for each data node in the NDB cluster. For each such row:
TABLESPACE_NAMEcolumns are all
PARTITION_COMMENTcolumns are empty.