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.
6
u/reditandfirgetit 1d 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
-6
u/iammen 1d ago
Yes, I am not going to admit that I have articulated the blog. But I do claim that the rough idea about what to cover and how much depth to cover is all mine. If you are curious, here is what I wrote in my notes for each of the articles.
And yes, it makes sense to include disclaimer or something if AI is used for the article. I will include it from now onwards.
4
0
u/shdw_0x0 1d 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!
7
u/B1zmark 1d ago
Written by Claude you mean?