← sources

Database indexes explained from first principles - a 3 part series

source: reddit (reddit:database)published 2026-11-14 06:00ingested 2026-11-15 02:36

retained text

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

[Read Part 1](https://raviutsav.github.io/writing/database-indexes-first-principles-part-1/)

**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

[Read Part 2](https://raviutsav.github.io/writing/database-indexes-query-plans-statistics-part-2/)

**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

[Read Part 3](https://raviutsav.github.io/writing/database-indexes-joins-part-3/)

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.

document fetched 2026-11-15 00:33 · canonical https://reddit.com/r/Database/comments/1xvuv86/database_indexes_explained_from_first_principles