Learn · SQL · Core
SQL Indexes
An index is why a database can find one row among 50 million in milliseconds. It’s the same idea as the index at the back of a book — and the same idea as binary search.
Before we start
- What a database index is and how it speeds lookups
- Why an index turns a full scan into a log-time seek
- The trade-off: faster reads, slower writes
- Which columns are worth indexing
- Explain why a query is slow and how an index fixes it
- Decide which columns to index in a schema
- Connect indexes to binary search / trees
Why you’re learning it: “why is this query slow?” is a real-job and interview staple, and the answer is almost always indexes. It also ties your Binary Search and Trees lessons to real systems. ⏱️ ~25 min.
The idea
Want to find every mention of “mitochondria” in a 900-page textbook. Without an index: read all 900 pages — a full scan. With the index at the back: jump straight to “page 412, 588.” A database index is exactly that — a sorted lookup structure (usually a B-tree, a tree that stays balanced) that points to where rows live. It turns a full table scan (O(n)) into a seek (O(log n)).
The trade-off — nothing is free
Lookups, filters (WHERE), joins and sorts on the indexed column become dramatically faster.
Every INSERT/UPDATE must also update the index. Indexes cost space and write time — so you don’t index everything.
What to index
- Columns you frequently filter by (
WHERE email = ...) - Columns you join on (foreign keys)
- Columns you sort by (
ORDER BY created_at) - Not columns that rarely appear in queries, or tables that are mostly writes
EXPLAIN before a query shows whether it’s using an index or scanning — the tool you reach for when something’s slow.
Where you’ll use it — real life
A page that crawls in production is often a query with no index doing a full scan. Adding one index can take it from 30s to 30ms.
Finding a user by email across millions of rows needs an index on email — instant instead of a scan.
Matching on a reference id is fast only if that column is indexed — your Recon project depends on it.
“Latest 100 orders” is cheap with an index on the date column, brutal without.
Now YOU do the reps
Out loud: “What does an index do, why does it make reads fast but writes slower, and which columns would I index?” Then log it in your Journal.
Next: SQL Transactions →