B-Tree Index Internals
What are the key trade-offs of B-Tree Index Internals? When would you choose it vs alternatives?
Answers use simple, clear English.
Audio N/AQuick interview answer
Pros: Efficient point and range lookups; supports ORDER BY on indexed column. Cons: Write amplification on insert/update; low-cardinality columns poor selectivity.
Detailed answer
Pros: Efficient point and range lookups; supports ORDER BY on indexed column. Cons: Write amplification on insert/update; low-cardinality columns poor selectivity. Decision: pick when benefits outweigh operational cost. Core: Default clustered/secondary indexes use B+ trees — balanced tree with sorted leaf pages linked for range scans. Seek O(log n); leaf scan for ranges. Real-time example: WHERE user_id = 42 uses index seek on user_id B-tree; BETWEEN dates scans leaf chain. Pros: Efficient point and range lookups; supports ORDER BY on indexed column. Cons: Write amplification on insert/update; low-cardinality columns poor selectivity. Common mistakes: Indexing every column; assuming hash index behavior on B-tree engine. Best practices: Index selective predicates and join keys; composite index column order matters. Audience level: Engineering Manager.
Full explanation
Default clustered/secondary indexes use B+ trees — balanced tree with sorted leaf pages linked for range scans. Seek O(log n); leaf scan for ranges.
Real example & use case
WHERE user_id = 42 uses index seek on user_id B-tree; BETWEEN dates scans leaf chain.
Pros & cons
Pros: Efficient point and range lookups; supports ORDER BY on indexed column. Cons: Write amplification on insert/update; low-cardinality columns poor selectivity.
Common mistakes
Indexing every column; assuming hash index behavior on B-tree engine.
Best practices
Index selective predicates and join keys; composite index column order matters.
Follow-up questions
- How would you test B-Tree Index Internals?
- What metrics prove B-Tree Index Internals is healthy in prod?
- How does B-Tree Index Internals change at 10× traffic?