Why does an index's internal data structure matter if it all ends up 'faster than a scan'?
Different structures are fast at different operations. A hash table is fast at exact lookups but can't do range scans at all. A B-tree does both reasonably well. A bitmap index is extremely compact for low-cardinality columns but terrible for high-cardinality ones. Choosing the wrong internal structure for your access pattern means the index technically exists but doesn't actually make your queries fast.
Answered in
What Actually Happens Inside a Database Index: The Data StructuresAn index trades disk I/O for lookup speed. Here's how B-trees, hash tables, and bitmap indexes each make that trade differently.
Read the full analysisOther questions this article answers
More system design questions
- Why does a database need an index at all — why can't it just scan the table?
- 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?
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.