Remove unused Indexes MariaDB 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.