20.4. The INFORMATION_SCHEMA STATISTICS Table

MySQL 5.0

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

previous page start next page