MySQL Show Indexes

As an administrator, you may want to know which indexes a table has. Use the SHOW INDEXES statement.

MySQL SHOW INDEXES syntax

To query the index information of a table, use the following SHOW INDEXES statement:

SHOW INDEXES FROM db_name.table_name;

or

SHOW INDEXES FROM table_name IN db_name;

You need some privilege on at least one column of the table to run this statement. See MySQL’s SHOW INDEX privilege requirement.

Explanation:

• db_name is the name of the database. It can be omitted if you have selected the database.
• table_name is the name of the table.
• The INDEXES keyword can be replaced with INDEX or KEYS.
• The IN keyword can be replaced with FROM.
• The FROM keyword can be replaced with IN.

WHERE clause

You can use a WHERE clause to filter the results:

SHOW INDEXES FROM db_name.table_name WHERE condition;

MySQL 8.4 SHOW INDEXES output

In MySQL 8.4, SHOW INDEXES returns the following 15 columns. The Expression field is available from MySQL 8.0.13, when functional key parts were introduced.

Table

Table Name

Non_unique

0 if the index does not permit duplicate values, or 1 if it permits duplicates.

Key_name

The name of the index. The name of the primary key index is fixed at PRIMARY.

If CREATE INDEX or ALTER TABLE ... ADD INDEX returns Error 1061, use Key_name to check whether the name is already used on that table. See MySQL Error 1061 troubleshooting.

Seq_in_index

The column ordinal in the index. The first column is numbered starting at 1.

Column_name

The column name

Collation

Indicates the index sort order: A is ascending, D is descending, and NULL means the column is not sorted.

Cardinality

Index cardinality, which is the estimated number of unique values ​​in the index. Note that this number is imprecise and only an estimate.

Note that the higher the cardinality, the greater the chance that the query optimizer will use the index for lookups.

Sub_part

The indexed prefix length for a partially indexed column, or NULL if the entire column is indexed.

Packed

Indicates how the key is packed; NULL if not.

Null

YES if the column may contain NULL Values, or blank if not.

Index_type

Index type. Possible values: BTREE, HASH, RTREE, or FULLTEXT.

Comment

Information about an index that is not described in its own column.

Index_comment

Displays comments for the index specified with the COMMENT attribute.

Visible

Whether the index is visible or invisible to the query optimizer; if visible YES, otherwise NO.

Expression

Contains the key-part expression instead of a column name for functional indexes; in that case, Column_name is NULL. It is NULL for ordinary column key parts.

MySQL SHOW INDEXES Examples

In the following examples, we use the film table from the Sakila sample database for demonstration.

show all indexes

To display all indexes in the film table, use the following statement:

SHOW INDEXES FROM sakila.film;

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film  |          0 | PRIMARY                     |            1 | film_id              | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_title                   |            1 | title                | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_fk_language_id          |            1 | language_id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_fk_original_language_id |            1 | original_language_id | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Filter indexes

You can use the WHERE clause to filter the results of SHOW INDEXES. For example, if you want to get all unique indexes from the film table, use the following statement:

SHOW INDEXES FROM sakila.film WHERE Non_unique = 0;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film  |          0 | PRIMARY  |            1 | film_id     | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Conclusion

In MySQL, indexes can improve the efficiency of querying data from tables. You can use the SHOW INDEXES statement to get the indexes of a table to know the index situation in the table. You can also filter the results of by using the WHERE clause in SHOW INDEXES statements.

For equivalent index-inspection commands in other database engines, see List Indexes by Database.