Beginner LevelSystem Design & Architecture
How do Database Indexes speed up reads at the cost of write performance?
❓ Interview Prompt: Explain B-Tree Database Indexes, $O(\log N)$ lookup speed, index maintenance overhead during INSERT/UPDATE/DELETE, and over-indexing risks.
A Database Index is a secondary data structure (usually a B-Tree) that allows the database engine to locate target rows without scanning the entire table:
Read vs Write Trade-Off:
•
Read Acceleration: Reduces query lookups from an $O(N)$ full table scan to an $O(\log N)$ index seek.
•
Write Penalty: Every 'INSERT', 'UPDATE', or 'DELETE' statement MUST update all secondary B-Tree indexes on the table, slowing down write throughput!
•
Rule: Never index every column; create indexes based strictly on query access patterns.
Code Implementation Example
markdown
B-Tree Index Lookup:
[ Root Node: 50 ]
/ \
[ Leaf: 10, 20 ] [ Leaf: 60, 80 ]
│
▼ (Direct Pointer to Disk Data Page)💡B-Tree index structure directing queries to target disk pages.
🎯 Interview Key Takeaway
Create secondary indexes selectively based on query filter access patterns to balance read acceleration against write penalties.
#System Design#Indexes#B-Tree#Performance#Beginner
Back to all System Design & Architecture Questions→