What is a database index and what are the trade-offs of adding one?

technical-conceptual · Junior level · software-engineering

What the interviewer is really asking

Assesses whether the candidate understands that performance optimizations have costs — a critical engineering judgment.

What to say

• An index is a separate data structure (often a B-tree) that lets the database find rows faster without a full table scan.
• Trade-offs: faster reads, but slower writes (index must be updated on INSERT/UPDATE/DELETE) and extra storage cost.
• Mention that over-indexing is a real problem — tables with many indexes on rarely-queried columns pay write overhead for no read benefit.

What to avoid

• Say indexes are always good and you should index every column.
• Ignore write overhead entirely.
• Confuse a primary key (unique, clustered) with a regular secondary index.

Example answers

Strong: On a users table with 10M rows, a query like WHERE email = ? without an index does a full table scan — O(n). Adding an index on email makes it O(log n). The trade-off is every INSERT or UPDATE to email now also updates the index.

Weak: I'd index every column up front — storage is cheap and you never know which columns you'll query later.