MySQL in a Nutshell

Name

SHOW INDEX

Synopsis

SHOW INDEX FROM table [FROM database]

This SQL statement will display information on the indexes for a given table. A table from a different database can be specified by either naming it with the table (e.g., database.table) or adding the FROM clause.

SHOW INDEX FROM contacts FROM sales_dept\G
*************************** 1. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: rec_id
   Collation: A
 Cardinality: 5
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:

In this example, instead of ending the statement with a semicolon, which would insert extra table formatting, a \G is used to alter the display to the format shown. This table has only one index, so only one is listed here. For each index, the table name is given. This is followed by a field indicating whether the index is nonunique. A unique index is indicated by 0. The name of the index or key (e.g., PRIMARY) is shown next. For indexes that use only one column, the key name and the column name are often the same. For indexes that use more than one column, a row will be listed for each column, each row having the same table name and the same key name. The sequence of the column in the table is given, where 1 is the first column. The name of the column or columns indexed is given next. Then the collation, how the column is sorted in the index, is specified. A value of A is given for ascending, D for descending. If the index is not sorted, ...