This post's content
Does it matter how many indexes I create?
As a general rule of thumb, the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be.
Indexes in MySQL (or any database for that matter) are not static. Every time we update the table (for example, using an INSERT query), the relevant indexes are updated by MySQL. Otherwise, they will be useless in the next search query that will need them.
Therefore, adding indexes shouldn't be taken lightly, as it's actually a performance trade off which must be balanced properly. The more indexes you add on a set of columns/table, the slower INSERT, UPDATE and DELETE statements will be. On the other hand, search operations will be optimized using those indexes.
This article will not describe the methodologies of choosing the correct indexes, but will teach you how to find and remove redundant indexes from your MySQL database.
How to track redundant indexes in MySQL?
Starting MySQL 5.6, the database keeps track of index usage as part of its PERFORMANCE SCHEMA. This data can be queried using the schema_unused_indexes view, which displays indexes for which there are no events. Having no events indicates that these indexes might be redundant and unused for a while.
One important point to keep in mind though, is that these stats are calculated all over again each time MySQL is restarted. Therefore, in order to get reliable information, you should query these views a while after your MySQL instance was started. How long after the startup you're asking? Well, that depends. the question you should ask yourself is: how busy your database is, and how complete is the time window you're inspecting? Do you know if all types of queries are usually executed in the database in a specific period of time? Were those "monthly reports" you forgot about executed as well during that time? If so, that's the window you should inspect.
So let's take a look at how it's done:
select * from sys.schema_unused_indexes;
| object_schema | object_name | index_name |
| mydb | age | agecountry_index |
| mydb | country | agecountry_index |
2 rows in set (0.01 sec)
How to drop a redundant index in MySQL?
Dropping the unused index is as easy as running this command:
DROP INDEX `index_name` on table
Summary
So we learned how to track down those unused indexes that slow down your database. Also, we had a glance at how to remove them. So, start optimizing that database!
Note: fetching the information from the performance schema is only possible if it's enabled.
You can also use mysqlindexcheck from the MySQL utilities to report on redundant indexes
There is a difference between "redundant" and "unused" indexes. mysqlindexcheck will only identify "redundant" indexes whereas performance_schema method can identify the "unused" ones.