Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA STATISTICS Table

22.23 The INFORMATION_SCHEMA STATISTICS Table

The STATISTICS table provides information about table indexes.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG def
TABLE_SCHEMA = Database
TABLE_NAMETable 
NON_UNIQUENon_unique 
INDEX_SCHEMA = Database
INDEX_NAMEKey_name 
SEQ_IN_INDEXSeq_in_index 
COLUMN_NAMEColumn_name 
COLLATIONCollation 
CARDINALITYCardinality 
SUB_PARTSub_partMySQL extension
PACKEDPackedMySQL extension
NULLABLENullMySQL extension
INDEX_TYPEIndex_typeMySQL extension
COMMENTCommentMySQL extension
INDEX_COMMENTIndex_commentMySQL 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
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
Sign Up Login You must be logged in to post a comment.