Information Schema STATISTICS Table

The Information Schema STATISTICS table provides information about table indexes.

It contains the following columns:

ColumnDescription
TABLE_CATALOGAlways def.
TABLE_SCHEMADatabase name.
TABLE_NAMETable name.
NON_UNIQUE1 if the index can have duplicates, 0 if not.
INDEX_SCHEMADatabase name.
INDEX_NAMEIndex name. The primary key is always named PRIMARY.
SEQ_IN_INDEXThe column sequence number, starting at 1.
COLUMN_NAMEColumn name.
COLLATIONA for sorted in ascending order, or NULL for unsorted.
CARDINALITYEstimate 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_PARTNULL if the whole column is indexed, or the number of indexed characters if partly indexed.
PACKEDNULL if not packed, otherwise how the index is packed.
NULLABLEYES if the column may contain NULLs, empty string if not.
INDEX_TYPEIndex type, one of BTREE, RTREE, HASH or FULLTEXT. See Storage Engine Index Types.
COMMENTIndex comments from the CREATE INDEX statement.
IGNOREDWhether 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:
...

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.