13.1.2 Obtaining Table Index Information - MySQL

To find out what indexes a table has, use SHOW CREATE TABLE to display the CREATE TABLE statement that corresponds to the table structure, including its indexes.

For more detailed information about the indexes, use SHOW INDEX. For example, SHOW INDEX produces the following output for the Country table of the world database:

mysql> SHOW INDEX FROM Country\G
********************* 1. row **********************
Table: Country
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Code
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

The output indicates that the table has a single index, a primary key on the Code column. The output for the City table is similar except that it indicates the ID column is the primary key:

mysql> SHOW INDEX FROM City\G
********************* 1. row **********************
Table: City
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

For the CountryLanguage table, the output has two rows because the primary key includes two columns, Country and Language:

mysql> SHOW INDEX FROM CountryLanguage\G
********************* 1. row ***************************
Table: CountryLanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Country
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
******************** 2. row ***************************
Table: CountryLanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Language
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

The Seq_in_index values show the order of the columns within the index. They indicate that the primary key columns are Country first and Language second. This information corresponds to the following PRIMARY KEY declaration:

PRIMARY KEY (Country, Language)