MySQL SHOW INDEX Statement

Overview

In this tutorial, we will learn about MySQL SHOW INDEX Statement. To view the status of indexes in a MySQL database table, we can use the SHOW INDEX Statement. An index assists MySQL server in finding the table rows more quickly and easily.

Syntax

The general syntax of the SHOW INDEX statement is as follows:

SHOW INDEX FROM <table_name>

Example

In this example, we will use the Country table in the world MySQL database. Index is created on a single column. The result of the SHOW INDEX statement done on the database table is as follows::

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: 239
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)

MySQL SHOW INDEX Statement

Multiple columns

Indexes can be created on multiple columns as well. We call this index as composite index.

In this example, we will use the CountryLanguage table in the world MySQL database. Index is created on multiple columns. The result of the SHOW INDEX statement done on the database table is as follows::

mysql> SHOW INDEX FROM CountryLanguage \G

*************************** 1. row ***************************
Table: countrylanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 233
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: countrylanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Language
Collation: A
Cardinality: 984
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: countrylanguage
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 233
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.00 sec)