Indexes are very helpful. Without them, MySQL has to scan the entire table to find the relevant rows and columns, which can be very inefficient in large databases.
This tutorial will focus on how to view index information by using the SHOW INDEXES clause in MySQL.
Show Table indexes
To show index information on a table, we use the SHOW INDEXES clause followed by the name of the table we wish to get the index information.
The general syntax is shown as:
For example, consider one of the tables in the Sakila sample database. We can get the index information as shown in the query below:
SHOW INDEXES FROM film;
The above query will display index information from the film table in the Sakila database. The output is:
Understanding Index Information
The SHOW INDEXES command displays the relevant information about the indexes in the table specified.
Here are the following terms and their respective information provided:
- Table: This is the first column from the output. It shows the name of the table where the index resides.
- Non-Unique: The second column shows if the index can contain a duplicate. The value is a Boolean, with 1 indicating the index can contain duplicates and 0 if otherwise.
- Key_name: The third column shows the name of the index. By convention, the primary key takes the index name of PRIMARY.
- Seq_in_index: The fourth column displays the column sequence number in the index starting from the value of 1.
- Column_name: The fifth column is simply the column name.
- Collation: The sixth column is a section that shows how the column gets sorted in the index. There are three sorting values, with A being the Ascending order, B indicating the Descending order, and NULL as non-sorted.
- Cardinality: The seventh column shows the uniqueness of the data value. In indexes, it shows the estimated number of unique values in the specific index.
- Sub_part: The eighth column displays the index prefix with NULL, indicating the entire column is indexed.
- Packed: The ninth column shows how the index keys are packed, with NULL indicating the keys are not packed.
- Null: The tenth column specifies if the column can contain NULL values. Yes, if the column can contain null values, and empty if not.
- Index_type: The eleventh column shows the index method such as BTREE, HASH, RTREE, and FULLTEXT.
- Comment: The twelfth column shows the information about an index not described in its column.
- Index_comment: The thirteenth column shows additional information about the index specified using the COMMENT attribute when created.
- Visible: The fourteenth column is the index visible to the query optimizer, with values of Yes and No.
- Expression: The fifteenth column displays if the index uses an expression and not a column or column prefix value.
HINT: The information about the indexes from the SHOW INDEXES query is similar to that of SQLStatistics.
Show Schema Indexes
You can also get index information about a schema. The general syntax for achieving this result is as below:
Consider the query below that shows information about the Sakila schema:
This will display information about the indexes in the Sakila schema as shown in the output below:
| TABLE_NAME | INDEX_NAME |
+---------------+-----------------------------+
| actor | PRIMARY |
| actor | idx_actor_last_name |
| address | PRIMARY |
| address | idx_fk_city_id |
| address | idx_location |
| category | PRIMARY |
| city | PRIMARY |
| city | idx_fk_country_id |
| country | PRIMARY |
| customer | PRIMARY |
| customer | idx_fk_store_id |
| customer | idx_fk_address_id |
| customer | idx_last_name |
| film | PRIMARY |
| film | idx_title |
| film | idx_fk_language_id |
| film | idx_fk_original_language_id |
| film_actor | PRIMARY |
| film_actor | PRIMARY |
| film_actor | idx_fk_film_id |
| film_category | PRIMARY |
| film_category | PRIMARY |
| film_category | fk_film_category_category |
| film_text | PRIMARY |
| film_text | idx_title_description |
| film_text | idx_title_description |
| inventory | PRIMARY |
| inventory | idx_fk_film_id |
| inventory | idx_store_id_film_id |
| inventory | idx_store_id_film_id |
|-----------------------------OUTPUT TRUNCATED--------------------------
You can also get information from all the schemas in the server using the query shown below:
NOTE: The above query dumps a lot of information. Rarely will you need to get indexes from all the schemas. However, a sample output is below:
| TABLE_NAME | INDEX_NAME |
+--------------------+------------+
| innodb_table_stats | PRIMARY |
| innodb_table_stats | PRIMARY |
| innodb_index_stats | PRIMARY |
| innodb_index_stats | PRIMARY |
| innodb_index_stats | PRIMARY |
+--------------------+------------+
Conclusion
In this tutorial, we discussed how to use the MySQL SHOW INDEXES query to get information about the indexes in a table. We also looked at using the information_schema to get information about the indexes from one or all schemas in a MySQL server.
