Why does a database need an index at all — why can't it just scan the table?
A table scan reads every row to find matches, so cost grows linearly with table size — fine for a thousand rows, unusable for a billion. An index is a precomputed structure that lets the database jump close to the matching rows instead of inspecting every one, the same way a book's index lets you skip straight to a page instead of reading cover to cover.
Answered in
Database Indexing Explained: Which Index Type Actually Fits Your QueryB-tree, hash, composite, and covering indexes each solve a different query shape. Pick the wrong one and you pay index overhead without the speedup.
Read the full analysisOther questions this article answers
More system design questions
- When is a hash index better than a B-tree index?
- What is a composite index and why does column order matter?
- What's a covering index and why is it faster than a normal index?
- What's the real cost of adding an index, beyond disk space?
- What does ACID actually stand for, and why do all four properties matter together?
- What's the practical difference between pessimistic and optimistic concurrency control?
- What is a race condition in a database transaction, and how does isolation prevent it?
- Why would a database ever allow a weaker isolation level than Serializable?
Every answer on Crashtech is written by the editor of the article it comes from — never auto-summarised. Browse all answers or the System Design beat.