Database Indexing easy
What is the primary performance penalty of adding many indexes to a table?
- A It significantly slows down Write operations (INSERT/UPDATE/DELETE) as every index must be synchronously updated.
- B It degrades Read performance (SELECT) by forcing the database to choose between too many competing execution paths.
- C It imposes a hard limit on the total number of rows that can be stored, eventually preventing the table from growing.
- D It forces the database engine to utilize more CPU cycles for low-level network negotiation and connection pooling.
Answer: A. It significantly slows down Write operations (INSERT/UPDATE/DELETE) as every index must be synchronously updated.
Every index attached to a table is a separate, synchronized data structure that must be maintained. When you insert a new row, the database must also find the correct insertion point in every B-Tree index for that table. If you have 10 separate indexes, a single `INSERT` operation effectively becomes 11 writes. This creates a critical trade-off: more indexes speed up complex reads but can severely starve a write-heavy system of performance.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?