List MySQL Indexes With INFORMATION_SCHEMA

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]’? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

This query uses the INNODB_SYS_TABLES, INNODB_SYS_INDEXES, and INNODB_SYS_FIELDS tables from INFORMATION_SCHEMA and is only available in MySQL 5.6 or Percona Server 5.5. However, it is much much faster than querying the STATISTICS table. It also only shows InnoDB tables.

SELECT t.name AS `Table`,
       i.name AS `Index`,
       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`
FROM information_schema.innodb_sys_tables t 
JOIN information_schema.innodb_sys_indexes i USING (table_id) 
JOIN information_schema.innodb_sys_fields f USING (index_id)
WHERE t.schema = 'sakila'
GROUP BY 1,2;

Assuming that all your tables are InnoDB, both queries will produce identical results. If you have some MyISAM tables in there, only the first query will provide complete results.

+---------------+-----------------------------+--------------------------------------+
| Table         | Index                       | Columns                              |
+---------------+-----------------------------+--------------------------------------+
| actor         | idx_actor_last_name         | last_name                            |
| actor         | PRIMARY                     | actor_id                             |
| address       | idx_fk_city_id              | city_id                              |
| address       | PRIMARY                     | address_id                           |
| category      | PRIMARY                     | category_id                          |
| city          | idx_fk_country_id           | country_id                           |
...
| rental        | rental_date                 | rental_date,inventory_id,customer_id |
| staff         | idx_fk_address_id           | address_id                           |
| staff         | idx_fk_store_id             | store_id                             |
| staff         | PRIMARY                     | staff_id                             |
| store         | idx_fk_address_id           | address_id                           |
| store         | idx_unique_manager          | manager_staff_id                     |
| store         | PRIMARY                     | store_id                             |
+---------------+-----------------------------+--------------------------------------+
42 rows in set (0.04 sec)

Tags: , , ,

One Response to “List MySQL Indexes With INFORMATION_SCHEMA”

  1. Dotan Cohen Says:

    Thank you Aaron! The first query is quite a find! Note that I just barely recognized the blog name from your profile on DBA.SE, though this post I found via Googling for MySQL indexes.

Leave a Reply