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)