SHOW INDEXES

This MySQL statement displays information about the indexes for a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SHOW {INDEX|INDEXES|KEYS} FROM table [FROM database]

Explanation

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

Examples

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: 

This table has only one index, so only one is listed here. 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 non-unique. A unique index is indicated by 0, a non-unique 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 informaiton 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 hull 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 section 'ALTER TABLE: Table options' for a description of packed keys.

If the column may contain 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