Database indexes explained from first principles - a 3 part series
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