0

I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use.

How to find those indexes and remove them?

I am a beginner with MariaDB and MySQL, so with the answer, if you could mention resources to study this, that would be great.

Improve this question
1

Refer to https://mariadb.com/kb/en/user-statistics/

An optional feature of MariaDB collects statistics on usage of indexes (and also tables, users, and clients).

If you enable the userstat plugin (see the documentation link), it records a counter of the rows that were read via each given index. If that number remains 0 for a given index, then it was not used by any query.

Keep in mind these counters are reset to 0 when the MariaDB server restarts, so you don't know if the index was used prior to that.

Improve this answer
4
  • Be cautious. If you have a query that really needs its index, but it is run only once a month, you might accidentally DROP it. (That is, there is no 'perfect' tool for the task.) – Rick James Mar 3 at 4:05
  • I agree with the recommendation to be cautious. It happened at my work recently that a developer dropped a needed index based on this kind of statistics check, and it took many hours to recreate the index, even with pt-online-schema-change. – Bill Karwin Mar 3 at 6:10
  • We do not have 'userstat' enabled on our MariaDB instance, as I am told it can have an affect performance of the DB, is there any other way to find the unused Indexes? also Thank you for ansswering – DB guy Mar 3 at 6:25
  • Read percona.com/blog/2012/06/02/how-expensive-is-user_statistics. I have enabled the userstat plugin on all production instances at my employer (we use Percona Server, not MariaDB, but the plugin is the same). – Bill Karwin Mar 3 at 6:50
1

Since your question is more of a research question than "how to", see also:

  • MySQL 8.0's INVISIBLE switch on indexes. This partially puts together two things mentioned by Bill, but still fails to completely and safely discover unused indexes.

  • https://jira.mariadb.org/browse/MDEV-7317 -- MariaDB's proposal to implement INVISIBLE (or IGNORE).

INVISIBLE lets you hide the index from the Optimizer -- Then if, as in Bill's anecdote, realize you need it, you don't have the pain of re-ADDing it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.