Information Schemas for Partitions

As part of the work in developing partitioning support for 5.1 a new
information schema table has been added. This table can be used to
retrieve information about properties of individual partitions.

To query this table you can issue a query like:
SELECT * FROM information_schema.partitions WHERE
table_schema = "database_name" AND table_name = "name_of_table";

The result of this particular query will be one record per partition in
the table with info about the properties of these partitions.

A query on a non-partitioned table will produce a similar output
although most fields will be NULL. The information_schema.partitions
table is not yet implemented for MySQL Cluster so for MySQL Cluster
tables the output will be all NULLs on the partition specific information.

Below follows a short description of the fields in this information
schema table:
1) TABLE_CATALOG: this field is always NULL
2) TABLE_SCHEMA: This field contains the database name of the table
3) TABLE_NAME: Table name
4) PARTITION_NAME: Name of the partition
5) SUBPARTITION_NAME: Name of subpartition if one exists otherwise
NULL
6) PARTITION_ORDINAL_POSITION: All partitions are ordered in the
same order as they were defined, this order can change as management
of partitions add, drop and reorganize partitions. This number is the
current order with number 1 as the number of the first partition
7) SUBPARTITION_ORDINAL_POSITION: Order of subpartitions within a
partition, starts at 1
8) PARTITION_METHOD: Any of the partitioning variants: RANGE, LIST,
HASH, LINEAR HASH, KEY, LINEAR KEY
9) SUBPARTITION_METHOD: Any of the subpartitioning variants: HASH,
LINEAR HASH, KEY, LINEAR KEY
10) PARTITION_EXPRESSION: This is the expression for the partition
function as expressed when creating partitioning on the table through
CREATE TABLE or ALTER TABLE.
11) SUBPARTITION_EXPRESSION: Same for the subpartition function
12) PARTITION_DESCRIPTION: This is used for RANGE and LIST partitions:
RANGE: Contains the value defined in VALUES LESS THAN. This is an
integer value, so if the CREATE TABLE contained a constant expression
this contains the evaluated expression, thus an integer value
LIST: The values defined in VALUES IN. This is a comma-separated list of
integer values.
13) TABLE_ROWS: Although its name indicates that it is the number of
rows in the table, it is actually the number of rows in the partition.
14) AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH,
INDEX_LENGTH, DATA_FREE, CREATE_TIME, UPDATE_TIME, CHECK_TIME,
CHECKSUM:
All these fields are to be interpreted in the same as for a normal table
except that the value is the value for the partition and not for the table.
23) PARTITION_COMMENT: Comment on the partition
24) NODEGROUP: This is the nodegroup of the partition. This is only
relevant for MySQL Cluster.
25) TABLESPACE_NAME: This is the tablespace name of the partition.
This is currently not relevant for any storage engine.