Menu
I have recently completely re-written a large project. In doing so, I have consolidated great number of random MySQL queries. I do remember that over the course of developing the previous codebase, I created indexes on a whim, and I'm sure there are a great number that aren't used anymore.
Is there a way to monitor MySQL's index usage to determine which indexes are being used, and which ones are not?
851
In Oracle SQL Developer, when you have SQL in the worksheet, there is a button "Explain Plan", you can also hit F10. After you execute Explain plan, it will show in the bottom view of SQL Developer. There is a column "OBJECT_NAME", it will tell you what index is being used.
Your answer To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.
You can list a table's indexes with the mysqlshow -k db_name tbl_name command. In MySQL 8.0. 30 and later, SHOW INDEX includes the table's generated invisible key, if it has one, by default.
Indexes are one of the most important features of the SQL Server while being most overlooked and misused in practice. Their proper use leads to great server performance, but having them set up incorrectly or not having them set up at all, leads to poor execution times.
I don't think this information is available in a stock MySQL installation.
Percona makes tools and patches for MySQL to collect index usage data.
See:
See also:
INDEX_STATISTICS table in the information_schematable_io_waits_summary_by_index_usage table in performance_schema in MySQL 5.6You may also be interested in a Java app called MySQLIndexAnalyzer, which helps to find redundant indexes. But this tool doesn't have any idea which indexes are unused.
148
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
