r/SQL • • 2d ago

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

Read 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

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

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.

23 Upvotes

8 comments sorted by

7

u/B1zmark 1d ago

Written by Claude you mean?

-7

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.

https://pastebin.com/bMCFNA20

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.

https://pastebin.com/bMCFNA20

4

u/mikeblas 1d ago

How is this better than https://use-the-index-luke.com/ ?

-1

u/iammen 1d ago

It's not. The use-the-index-luke is definitely more complete and better.

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!