What is the impact of indexes on INSERT, UPDATE, and DELETE operations?

Short answer: Indexes can slow down data modification operations: INSERT: When a new row is inserted, the index must also be updated to include the new value, adding extra overhead.

Explain a bit more

UPDATE: If the indexed column is updated, the index must be modified to reflect the new value, which can be slower. DELETE: When a row is deleted, the corresponding index entries must be removed, causing additional overhead. While indexes speed up SELECT queries, they can reduce the performance of INSERT, UPDATE, and DELETE operations due to the extra work needed to maintain the index.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.