Month: October 2013

Listing MySQL indexes from information_schema

Today I needed to find out to what table a given index was specified on an existing database to which I was not very familiarized with. To do so, we may use the information_schema to list what we need (the following statement was adapted from this link).

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 = '<database_schema>' and 
       index_name = '<index_name>' 
GROUP BY 1,2;
Advertisements