Information Schema STATISTICS Table
The Information Schema STATISTICS
table provides information about table indexes.
It contains the following columns:
Column
Description
TABLE_CATALOG
Always def.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
NON_UNIQUE
1 if the index can have duplicates, 0 if not.
INDEX_SCHEMA
Database name.
INDEX_NAME
Index name. The primary key is always named PRIMARY.
SEQ_IN_INDEX
The column sequence number, starting at 1.
COLUMN_NAME
Column name.
COLLATION
A for sorted in ascending order, or NULL for unsorted.
CARDINALITY
Estimate of the number of unique values stored in the index based on statistics stored as integers. Higher cardinalities usually mean a greater chance of the index being used in a join. Updated by the ANALYZE TABLE statement or myisamchk -a.
SUB_PART
NULL if the whole column is indexed, or the number of indexed characters if partly indexed.
PACKED
NULL if not packed, otherwise how the index is packed.
NULLABLE
YES if the column may contain NULLs, empty string if not.
INDEX_TYPE
Index type, one of BTREE, RTREE, HASH or FULLTEXT. See Storage Engine Index Types.
COMMENT
Index comments from the CREATE INDEX statement.
IGNORED
Whether or not an index will be ignored by the optimizer. See Ignored Indexes. From MariaDB 10.6.0.
The SHOW INDEX statement produces similar output.
Example
SELECT * FROM INFORMATION_SCHEMA.STATISTICS\G
...
*************************** 85. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: table1
NON_UNIQUE: 1
INDEX_SCHEMA: test
INDEX_NAME: col2
SEQ_IN_INDEX: 1
COLUMN_NAME: col2
COLLATION: A
CARDINALITY: 6
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
...
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?