SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
SHOW INDEX returns table index
information. The format resembles that of the
SQLStatistics call in ODBC. This statement
requires some privilege for any column in the table.
SHOW INDEX returns the following
fields:
TableThe name of the table.
Non_unique0 if the index cannot contain duplicates, 1 if it can.
Key_nameThe name of the index.
Seq_in_indexThe column sequence number in the index, starting with 1.
Column_nameThe column name.
How the column is sorted in the index. In MySQL, this can have values “
A” (Ascending) orNULL(Not sorted).An estimate of the number of unique values in the index. This is updated by running
ANALYZE TABLEor myisamchk -a.Cardinalityis counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.Sub_partThe number of indexed characters if the column is only partly indexed,
NULLif the entire column is indexed.PackedIndicates how the key is packed.
NULLif it is not.NullContains
YESif the column may containNULL. If not, the column containsNO.Contains
YESif the column may containNULLvalues and''if not.Index_typeThe index method used (
BTREE,FULLTEXT,HASH,RTREE).CommentVarious remarks.
Index_commentAny comment provided for the index with a
COMMENTattribute when the index was created.
You can use
db_name.tbl_name
as an alternative to the
tbl_name FROM
db_name
SHOW INDEX FROM mytable FROM mydb; SHOW INDEX FROM mydb.mytable;
You can also list a table's indexes with the mysqlshow
-k db_name
tbl_name command.