MySQL Show Indexes

In MySQL, you can use the SHOW INDEXES command to all indexes from a table.

As a administrator, you may want to know how many indexes in a table. You can 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;

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 the SHOW INDEXES statement with the WHERE statement to filter the results. as follows:

SHOW INDEXES FROM db_name.table_name WHERE condition;

SHOW INDEXES Output

The MySQL SHOW INDEXES statement returns the following 15 columns:

Table
Table Name
Non_unique
Is it a unique index. 1, if no, otherwise 1.
Key_name
The name of the index. The name of the primary key index is fixed at PRIMARY.
Seq_in_index
The column ordinal in the index. The first column is numbered starting at 1.
Column_name
The column name
Collation
The collation indicates how the columns are ordered in the index. A indicates ascending order, B indicates descending order, or NULL indicates no sorting.
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
Index prefix. True if the entire column is indexed. Otherwise NULL, it displays the index character count if the column is partially 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
Has value if the index uses expressions instead of columns or column prefix values. If this has value, the column_name column is NULL.

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.