# Database indexes explained from first principles - a 3 part series — retained record

> Retained reddit record from reddit:database.

Canonical: https://highsignal.app/data/records/3ee90aa3902f9d16

[← sources](https://highsignal.app/data)


source: [reddit](https://highsignal.app/data/reddit) (reddit:database)  published 2026-11-14 06:00 ingested 2026-11-15 02:36

[original source ↗](https://www.reddit.com/r/Database/comments/1xvuv86/database_indexes_explained_from_first_principles/)

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