The project has a table in which developers once created many indexes. Obviously, through ignorance. Now it slows down the base.

Delete all indexes scary. First, because of the size of the table, deleting and adding an index takes considerable time. Secondly, the site under load and the removal of the "desired" index can cause serious problems.

Perhaps there is some way to monitor the use of the index? It would be useful to know how often selects refer to the index. Ideally, which queries use an index.

  • "which queries use the index" will show explain select... If you have a bunch of extra indexes, then you can only advise to do one thing: delete all indexes (leave only PK), analyze all queries and add those indexes that you would like to add. If in doubt, then do not add, without any doubt. When the minimum set of really necessary indexes is obtained, then already look at the tests how the queries behave. You may have to add a couple more indexes. Then close the access to the database for all the "maintenance", update the indexes and open access - BOPOH
  • "which queries use the index" and not which indexes the query uses. Explain does not help here. I already described the problems of approach with "delete all indexes". Analyze all requests is not possible. Because different backends use base. I agree, you can turn on the log, then somehow group and track each request. But this is a different approach. - Rjazhenka
  • take your queries that are executed frequently or must be executed quickly and explain for them, then you will see which indices are used. Those. you have to do this work yourself. In any case, it will have to be carried out when you look for what indexes should be added. And so that there are no "problems" due to the removal of all indexes - and enter "maintenance", as an option - just readonly access, perform all operations with indexes on the spare database, and then fill in the dump into the working database. At the same SO, such “reading modes” periodically occur. You are not writing software for rockets? ) - BOPOH

1 answer 1

I found the answer myself. In this article.

The easiest way to get rid of unnecessary indexes is the check-unused-keys utility.