Optimizing for Query Speed - 13.1.2 Obtaining Table Index Information
(Page 3 of 7 )

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)

Next: 13.1.3 Using Indexes >>
 

More MySQL Articles
More By Sams Publishing

Comments On: Optimizing for Query Speed

· This article is an excerpt from the book "MySQL Certification Guide," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.