Table of Contents for

MySQL in a Nutshell, 2nd Edition

This SQL statement displays information about the indexes for a given table. A table from a different database can be specified either by preceding the table name with the database name and a dot (e.g., database.table) or by adding the FROM clause. The INDEXES keyword may be replaced with INDEX or KEYS—all three are synonymous:

SHOW INDEXES FROM contacts FROM sales_dept \G

*************************** 1. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: contact_id
   Collation: A
 Cardinality: 265
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: contact_name
Seq_in_index: 1
 Column_name: name_last
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: contact_name
Seq_in_index: 2
 Column_name: name_first
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

Looking at these results, we can see that 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, a nonunique index by 1. The name of the index or key (i.e., PRIMARY or contact_name in the example) 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 (i.e., name_last and name_first for contact_name).

The output gives the sequence of the columns in the index, where 1 is the first column. The name of the column (or columns) indexed is next, followed by the collation (how the column is sorted in the index). A value of A means ascending and D means descending. If the index is not sorted, the Collation field value is NULL.

The Cardinality field is based on the number of unique indexes contained in the column. The server consults this information to determine whether to use an index in a join. The higher the cardinality, the more likely it will be used.

The Sub_part field indicates the number of characters of the column that are indexed for partially indexed columns. This field is NULL if the NULL column is indexed.

The Packed field indicates how the key is packed. If the key is not packed, the field has a value of NULL. See the earlier subsection ALTER TABLE: Table options” for a description of packed keys.

If the column may contain a NULL value, the Null field reads Yes; otherwise, it’s empty. Index_type is the structure of the index, which can be BTREE, HASH, FULLTEXT, RTREE (as of version 5.0.1 of MySQL), or SPATIAL. The Comments field contains any comments associated with the index.

From the command line, the mysqlshow utility with the --keys option can be used to show the same information:

mysqlshow --user=user --password --keys database table