Problem Statement
Indexes speed reads, but they slow writes. Explain how and give two mitigation strategies.
Explanation
On every INSERT, UPDATE, or DELETE, each affected index must be updated. This adds CPU, I/O, and potential page splits. Over-indexed tables show slower bulk loads and heavier lock contention under write-heavy workloads.
Mitigate by indexing only what you query often, and by consolidating composite indexes to cover multiple access patterns. For large one-time loads, disable or drop non-essential indexes and recreate them after the load. Batch writes and keep transactions short to reduce blocking.
Code Solution
SolutionRead Only
/* Strategy example: create minimal indexes first, add others after profiling */
Practice Sets
This question appears in the following practice sets:
