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
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).
- 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.
- 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.
- 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.
- 1
ALTER TABLE operations take longer.
- 2
Rebuilding, optimizing, or analyzing tables becomes costly.
- 3
Replication lag increases when DDL operations become heavy.
- 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.
Scenario Questions
0-2 years experience
- 1You 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?
- 2If 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
- 1Your 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?
- 2During 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?
- 3Explain how MySQL’s optimizer chooses an index and why having many similar indexes can lead to suboptimal plans.
5-8 years experience
- 1Design 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.
- 2How would you balance the need for fast analytical queries with the write latency impact of indexes in a sharded MySQL cluster?
- 3If 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
- 1Across 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?
- 2When 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?
- 3Describe 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?