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;
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s