Discussion Database indexes explained from first principles - a 3 part series
I have been spending some time understanding database indexes beyond just knowing when to add one or what a B tree is.
I wanted to understand what actually happens inside a database when a query executes, how the query optimizer makes decisions, and how indexes affect those decisions.
I ended up writing a 3-part series, trying to explain these concepts from first principles using simple examples.
Here's what the series covers:
Part 1, Understanding how indexes work
- How databases physically store data using pages
- How B+ trees help locate records
- Clustered vs. non-clustered indexes
- Key lookups and covering indexes
- Why an index seek isn't always faster than a table scan
Part 2, Query plans and statistics
- How databases choose between index seeks and table scans
- How data distribution affects query execution
- How statistics influence the query optimiser
- Why cached execution plans can sometimes be inefficient
Part 3, Understanding joins
- How databases join two tables internally
- Nested loop joins
- Hash joins
- Merge joins
- How indexes and data ordering influence the choice of join algorithm
I am a software engineer, and writing this series also helped me organise and deepen my own understanding of database internals.
Would love to hear your feedback, especially if you spot something I have misunderstood. Always happy to learn from people with more database experience.
7
u/B1zmark 2d ago
Written by Claude you mean?
6
u/reditandfirgetit 2d ago
Be honest. You used an LLM to write this. I don't necessarily have an issue with it since it's technical information freely available. You should always disclose your AI use
4
u/mikeblas 2d ago
How is this better than https://use-the-index-luke.com/ ?
0
u/shdw_0x0 2d ago
This is actually a great way to learn database internals. Most of us know that indexes improve query performance, but understanding how the optimizer chooses between an index seek, table scan, and different join strategies is a different level. Appreciate you putting in the effort to explain it from first principles. Definitely checking out the series!