Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 30.3Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.9Kb
Man Pages (Zip) - 293.1Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

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

21.21 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

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.