The STATISTICS view contains details about table indexes. Values in the columns that represent table statistics are cached.
| Column | Description |
|---|---|
TABLE_CATALOG |
The catalog where the table containing the index resides. This value is always def. |
TABLE_SCHEMA |
The schema (database) where the table containing the index resides. |
TABLE_NAME |
The name of the table containing the index. |
NON_UNIQUE |
Indicates whether the index can contain duplicates: 0 if it can’t, 1 if it can. |
INDEX_SCHEMA |
The schema (database) to which the index belongs. |
INDEX_NAME |
The name of the index. If it’s the primary key, the name is PRIMARY. |
SEQ_IN_INDEX |
The position of the column in the index, starting from 1. |
COLUMN_NAME |
The name of the indexed column. For functional key parts, COLUMN_NAME is NULL. For nonfunctional parts, COLUMN_NAME shows the column indexed by the key part. |
COLLATION |
The sorting order of the column in the index: A (ascending), D (descending), or NULL (not sorted). |
CARDINALITY |
An estimate of the unique values in the index. Cardinality is calculated as an integer. |
SUB_PART |
The number of indexed characters if the column is partly indexed. NULL if the entire column is indexed. |
PACKED |
Indicates how the key is packed. NULL if it’s not. |
NULLABLE |
Indicates whether the column can contain NULL values: YES if it can, NO if not. |
INDEX_TYPE |
The method used for the index: BTREE, FULLTEXT, HASH, or RTREE. |
COMMENT |
Additional information about the index, such as disabled if the index is disabled. |
INDEX_COMMENT |
The comment provided when the index was created. |
IS_VISIBLE |
Indicates whether the index is visible to the optimizer. |
EXPRESSION |
For functional key parts, the key expression. NULL for nonfunctional parts. |
You can also use the SHOW INDEX statement to return information about table indexes.
Information about generated invisible primary key (GIPK) columns is included in the INFORMATION_SCHEMA.STATISTICS view by default. You can hide these columns by setting the show_gipk_in_create_table_and_information_schema system variable to OFF.
For more information, see the MySQL documentation: The INFORMATION_SCHEMA STATISTICS Table.