MySQL Show Indexes

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;

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.

MySQL SHOW INDEXES syntax