MySQL MariaDB

How to Show All Indexes in MySQL or Schema

MySQL database index refers to a type of data structure used as data organization in a database and to help advance the speed of various operations carried out in MySQL.

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:

SHOW INDEXES tbl_name;

For example, consider one of the tables in the Sakila sample database. We can get the index information as shown in the query below:

USE sakila;

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:

  1. Table: This is the first column from the output. It shows the name of the table where the index resides.
  2. 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.
  3. Key_name: The third column shows the name of the index. By convention, the primary key takes the index name of PRIMARY.
  4. Seq_in_index: The fourth column displays the column sequence number in the index starting from the value of 1.
  5. Column_name: The fifth column is simply the column name.
  6. 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.
  7. 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.
  8. Sub_part: The eighth column displays the index prefix with NULL, indicating the entire column is indexed.
  9. Packed: The ninth column shows how the index keys are packed, with NULL indicating the keys are not packed.
  10. Null: The tenth column specifies if the column can contain NULL values. Yes, if the column can contain null values, and empty if not.
  11. Index_type: The eleventh column shows the index method such as BTREE, HASH, RTREE, and FULLTEXT.
  12. Comment: The twelfth column shows the information about an index not described in its column.
  13. Index_comment: The thirteenth column shows additional information about the index specified using the COMMENT attribute when created.
  14. Visible: The fourteenth column is the index visible to the query optimizer, with values of Yes and No.
  15. 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:

SELECT table_name, index_name FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = “schema_name”;

Consider the query below that shows information about the Sakila schema:

SELECT table_name, index_name FROM information_schema.statistics WHERE table_schema = "sakila";

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:

SELECT table_name, index_name FROM information_schema.statistics;

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list