What are the trade-offs of adding too many indexes to a table?

Trade-Offs and Downsides of Adding Too Many Indexes in MySQL

Indexes speed up read queries, but adding too many can significantly harm overall database performance and resource usage. MySQL must maintain every index during inserts, updates, and deletes, which creates measurable overhead.

1. Slower INSERT, UPDATE, DELETE Operations

1
Each write operation must update every relevant index.
2
More indexes = more B-tree updates = slower modifications.
3
High-write workloads suffer the most (e.g., logging, real-time apps).

2. Increased Storage Usage

1
Indexes consume disk space, sometimes more than the table itself.
2
Composite and full-text indexes can grow very large.
3
More storage increases backup time and replication lag.

3. Higher Memory Consumption

1
Indexes compete for buffer pool memory.
2
Too many indexes reduce available memory for caching table data.
3
This can lead to more disk reads and poorer performance.

4. Reduced Query Optimizer Accuracy

1
More indexes give MySQL more choices, sometimes leading to suboptimal plans.
2
Poor selectivity indexes may confuse the optimizer.
3
Bad index selection can slow down otherwise fast queries.

5. Longer Index Rebuild and Maintenance Time

1
ALTER TABLE operations take longer.
2
Rebuilding, optimizing, or analyzing tables becomes costly.
3
Replication lag increases when DDL operations become heavy.

6. Fragmentation Issues

1
More indexes can lead to increased fragmentation.
2
Fragmentation decreases performance and may require maintenance (OPTIMIZE TABLE).

In short, indexes improve read performance but degrade write performance and consume more resources. Adding only necessary, well-designed indexes ensures good overall database performance.

Difficulty: 5/10

Topics: index maintenance overhead, write performance impact, storage and query planning

Scenario Questions

0-2 years experience

1
You have a users table with 1 million rows and need to speed up lookups by email. How would you add an index, and what immediate effect would you expect on inserts?
2
If you added indexes on both first_name and last_name columns, what would happen to a query that filters on both columns together?

2-5 years experience

1
Your recent deployment caused a noticeable slowdown on write‑heavy API endpoints. You suspect the new indexes are the cause. How would you investigate and decide which indexes to drop?
2
During a migration, a query that used to run in 200 ms now takes 2 seconds after you added several composite indexes. What could be going wrong, and how would you fix it?
3
Explain how MySQL’s optimizer chooses an index and why having many similar indexes can lead to suboptimal plans.

5-8 years experience

1
Design a strategy for managing index bloat in a high‑traffic e‑commerce platform that writes thousands of orders per second and also runs complex reporting queries.
2
How would you balance the need for fast analytical queries with the write latency impact of indexes in a sharded MySQL cluster?
3
If you need to add a new index to a 500 GB table without downtime, what steps would you take and what trade‑offs are involved?

8+ years experience

1
Across multiple services, you notice that each team creates its own indexes on shared tables, leading to redundancy and storage waste. How would you establish a governance model to control index proliferation?
2
When planning a migration from MySQL to a distributed SQL database, how would you evaluate the existing index strategy and decide which indexes to keep, consolidate, or drop?
3
Describe the long‑term maintenance implications of aggressive indexing on schema evolution, CI pipelines, and operational monitoring.

Follow-up Questions

❓What metrics would you track to know an index is hurting performance?
❓How do you decide between a single‑column index and a composite index?
❓Can you describe a situation where dropping an index improved overall system latency?