Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
