But here’s the catch: too many indexes can actually hurt your database - in ways that aren't obvious at first.
In this blog, we’ll deep dive into the hidden costs of excessive indexing in MySQL, how to detect them, and how to fix that problem.
While we know that indexes help speed up SELECTs, they come at a cost, which include:
Every time you do an INSERT, UPDATE, or DELETE query for a row, MySQL must update every affected index, which basically means:
More indexes = more disk I/O and CPU usage.
Example:
If you have 8 indexes on a heavily written table, even a single UPDATE to one column can cause all relevant index pages to be rewritten
In high-concurrency environments, index maintenance increases row-level and index-level locking, which can reduce throughput and increase transaction wait times.
Indexes consume disk and redundant or unused indexes can double or triple your storage footprint, especially on large tables.
Altering a table with many indexes can become painfully slow, especially if you're adding or dropping columns or running a full table rebuild
More indexes means more choices for the query optimizer, and that increases the chance it picks a suboptimal plan.
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;
This option is available from MySQL 8.0+ with the performance_schema and sys schema enabled. This query basically retrieves a list of indexes from the sys.schema_unused_indexes view in MySQL that have not been used since the last server restart.
Sometimes indexes are subsets of one another, like below:
KEY idx_user_email (email,user_id)
KEY idx_email (email)
In this case, idx_email is redundant, because email is already covered by the first one
To avoid this, use tools like:
Instead of creating multiple single-column indexes, try using a composite index that covers your common WHERE + ORDER BY clauses.
Ref blog for Invisible indexes - https://www.pythian.com/blog/technical-track/mysql-8-0-invisible-indexes
Before dropping an index, make it invisible and observe if query performance degrades.
ALTER TABLE my_table ALTER INDEX my_index INVISIBLE;
Review index usage every few months, especially after any major feature releases
If your workload is write-heavy, lean toward fewer indexes. If it’s read-heavy, optimize for query patterns with relevant indexes.
You add multiple indexes to a table with, lets say 1 million rows, to speed up analytics queries. Monitor the performance and within sometime, the below possible effects can be noticed:
Try removing those unused indexes and the performance shall surely rebound, and storage would drop significantly.
Indexes are powerful, but like all powerful tools, they must be used with care. Always follow below few things while indexing:
While indexes are essential for performance, having too many can silently degrade your database performance. They increase write overhead, consume storage, and may confuse the optimizer at times. Regular audits, smart indexing strategies, and testing with invisible indexes will help keep your schema lean and performant. Remember—indexing is about precision, not quantity.
Looking to optimize your MySQL use?