Skip navigation links
MySQL 5.4 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.4 The INFORMATION_SCHEMA STATISTICS Table
19.5 The INFORMATION_SCHEMA USER_PRIVILEGES Table »
Section Navigation      [Toggle]

19.4. The INFORMATION_SCHEMA STATISTICS Table

The STATISTICS table provides information about table indexes.

INFORMATION_SCHEMA Name SHOW Name Remarks
TABLE_CATALOG   NULL
TABLE_SCHEMA   = Database
TABLE_NAME Table  
NON_UNIQUE Non_unique  
INDEX_SCHEMA   = Database
INDEX_NAME Key_name  
SEQ_IN_INDEX Seq_in_index  
COLUMN_NAME Column_name  
COLLATION Collation  
CARDINALITY Cardinality  
SUB_PART Sub_part MySQL extension
PACKED Packed MySQL extension
NULLABLE Null MySQL extension
INDEX_TYPE Index_type MySQL extension
COMMENT Comment MySQL extension

Notes:

  • There is no standard table for indexes. The preceding list is similar to what SQL Server 2000 returns for sp_statistics, except that we replaced the name QUALIFIER with CATALOG and we replaced the name OWNER with SCHEMA.

    Clearly, the preceding table and the output from SHOW INDEX are derived from the same parent. So the correlation is already close.

The following statements are equivalent: 

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_name = 'tbl_name'
  AND table_schema = 'db_name'

SHOW INDEX
  FROM tbl_name
  FROM db_name

User Comments

Posted by jaap taal on August 8 2008 11:01am[Delete] [Edit]

Quite handy if you want to view all indices in a database:

SELECT table_name, index_name, column_name FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = 'dbname' ORDER BY table_name, index_name, seq_in_index

Add your own comment.