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 
       table_schema = '<database_schema>' and 
       index_name = '<index_name>' 

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s