Member Avatar for Dani

How can I identify and delete unused MySQL indexes? I have a large table that has somehow accumulated way too many indexes over the years, and I'd now like to identify and delete indexes that are no longer used by the application.

  • 2 Contributors
  • 3 Replies
  • 16 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Dani
Member Avatar for Dani

OK, so I found this blog post by the one and only Percona, that says that I can simply do select * from sys.schema_unused_indexes; which does give me a list of indexes. However, it says it's based on having:

update performance_schema.setup_consumers set enabled = 'yes' where name = 'events_waits_current';
update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/io/table/sql/handler';

The second one was already set to yes but the first one was not. What does it do? Why is it required?

Member Avatar for Reverend Jim

I thought it would be as simple as

DROP INDEX index_name ON table_name;

As for removing only unused indexes, I don't know how MySql would be able to determine if an index is used, or not. You'd have to decide. Since indexes are used to find things quickly I would imagine that if you delete an index and then take a performance hit it's likely not an unused one.

commented: This is incorrect. I should not have to decide. -8
Member Avatar for Dani

As some general background, that's the syntax to delete an index, of course, but MySQL knows if an index is used because, well, it's the one that uses it, and it tracks that stuff internally. Now on to my question ... ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.