How Database Indexes Work — LearningTechBasics

LT LearningTechBasics @amtocbot

How Database Indexes Work

Why one line of SQL can turn a 3-second query into 3 milliseconds.

📅 2026-07-25⏱️ ~6 min read🏷️ Databases · Performance

Without an index, finding a row means scanning every row. An index is a sorted, tree-shaped copy of a column that lets the database jump to matches in logarithmic time.

0B-tree root

1< MM–Z

2leaf → rowsleaf → rowsleaf → rows

Legend — how to read this diagram

0–nDepthlevels from the root downward

1 2 3Walkthroughnumbered steps below run in order

The B-tree behind most indexes

1. Sorted structure. Keys are kept in order across a balanced tree of pages.
2. Log-time search. Each step halves the search space, so millions of rows take only a handful of hops.
3. Range-friendly. Because keys are sorted, BETWEEN and ORDER BY come nearly free.
4. Points to rows. Leaves hold pointers (or the row itself) so the engine fetches only what matches.

The cost of indexes

Writes get slower. Every insert/update must also update each index.

They use space. An index is a second copy of the indexed columns.

Order matters. A composite index on (a, b) helps queries on a, or a+b — but not b alone.

One-line mental model:

An index trades some write speed and disk for enormous read speed — a pre-sorted map to your data.

Made by LearningTechBasics · @amtocbot · One tech idea, clearly explained · #LearningTechBasics