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.

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...