The hidden cost of too many indexes in MySQL

Indexes are great, right? They make your queries faster, improve lookups, and are essential to performance tuning.

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.

Indexes are not free

While we know that indexes help speed up SELECTs, they come at a cost, which include:

1. Slower write performance

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

2. Increased lock contention

In high-concurrency environments, index maintenance increases row-level and index-level locking, which can reduce throughput and increase transaction wait times.

3. Wasted disk space

Indexes consume disk and redundant or unused indexes can double or triple your storage footprint, especially on large tables.

4. Longer DDL times

Altering a table with many indexes can become painfully slow, especially if you're adding or dropping columns or running a full table rebuild

5. Optimizer confusion

More indexes means more choices for the query optimizer, and that increases the chance it picks a suboptimal plan.

How to detect index overload?

1. Find unused indexes

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.

2. Check for redundant indexes

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:

• pt-duplicate-key-checker (from Percona Toolkit)
  
  
• MySQL Workbench’s “Index Statistics”
  
  

Best practices for indexing

1. Use Composite indexes wisely

Instead of creating multiple single-column indexes, try using a composite index that covers your common WHERE + ORDER BY clauses.

2. Use invisible indexes for testing

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;

3. Audit your schema regularly

Review index usage every few months, especially after any major feature releases

4. Balance read vs write

If your workload is write-heavy, lean toward fewer indexes. If it’s read-heavy, optimize for query patterns with relevant indexes.

A test case when indexes could backfire: 

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:

• Writes became comparatively slower
  
  
• Some regular jobs can begin to time out
  
  
• Simple schema changes can take more hours to finish

Try removing those unused indexes and the performance shall surely rebound, and storage would drop significantly.

Summary

Indexes are powerful, but like all powerful tools, they must be used with care. Always follow below few things while indexing:

• Monitor index usage
• Test before dropping indexes
• Don’t index blindly
• Audit regularly

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.

MySQL Database Consulting

Looking to optimize your MySQL use?