Collecting a list of current MySQL index in a database
In MySQL, there is an easy way to get all index of a particular table.
SHOW INDEX FROM table_name;
But what if you want to list all index’s from all tables in a database so you can find out what you have and where you’re missing some? If you have 20 or more tables it’ll get tiring after a while running the SHOW INDEX command on each.
Thankfully, the information is stored in another table MySQL uses called information_schema. You can get a list of all the index’s, along with the table name and field names, by running the following (change “your_database”).
SELECT table_name, column_name, index_name FROM information_schema.statistics WHERE index_name != ‘primary’ and table_schema = ‘your_database’;
That’s it. A list of what table, what field, and the name that each index applied to. Happy indexing.
