Indexes & Performance
To find every mention of "normalization" in a 400-page textbook, you don't read the book cover to cover — you flip to the index at the back, find the term, and jump straight to the right pages. A database index works the same way: it's a separate, sorted structure that lets the database jump straight to matching rows instead of scanning the entire table one row at a time.
💡 The Bottom Line
An index is a sorted lookup structure built on one or more columns that lets the database
find matching rows quickly instead of scanning the whole table. Indexes make reads
(SELECT with a WHERE or JOIN on that column) much
faster, at the cost of extra storage and slightly slower writes, since every
INSERT/UPDATE/DELETE has to keep the index up to date
too. Add an index where you filter or join often; don't add one on every column "just in
case."
Without an index: scanning every row
Say customers has a million rows and no index on email. This
query:
SELECT * FROM customers WHERE email = '[email protected]';has to check every single row's email column, one at a time, until it finds a
match (or exhausts the table) — this is called a full table scan. On a small
table that's instant; on a million rows, it's slow, and it gets slower as the table grows.
With an index: jumping straight there
CREATE INDEX idx_customers_email ON customers(email);Now the database maintains a sorted structure mapping email values to row locations. The same query can jump almost directly to the matching row, similar to how you'd use a phone book's alphabetical order instead of reading every entry front to back.
🙋 There Are No Dumb Questions
Q: If indexes make lookups so much faster, why not index every column?
A: Because indexes aren't free. Each one takes extra disk space, and every time you insert,
update, or delete a row, the database has to update every index on that table too — not just
the table itself. Indexing every column would make writes noticeably slower for the sake of
speeding up reads that may never actually happen on some of those columns.
The tradeoff: faster reads, slower writes
This is the core tradeoff to internalize:
- Reads get faster — queries filtering or joining on an indexed column can skip the full scan.
- Writes get slightly slower — every insert/update/delete has to update the index too, not just the underlying table.
- Storage grows — the index itself takes disk space, sometimes substantial on a large table.
⚠️ Watch Out
An index only helps the queries that actually filter, join, or sort on the indexed
column(s). Adding an index on customers.name does nothing to speed up a query
filtering on customers.email. Look at your slow queries' WHERE and
JOIN ON clauses first, then index those specific columns — don't guess.
When to add one
Good candidates for an index: columns you frequently filter on (WHERE
status = 'active'), columns you frequently join on (foreign keys like
orders.customer_id), and columns you frequently sort by. Primary keys are indexed
automatically by most databases — that's part of why looking a row up by its id is
already fast without you doing anything.
🔧 Try It Yourself
Look back at the orders table from the JOIN lesson. Its queries repeatedly
join on orders.customer_id. Write the CREATE INDEX statement you'd
use to speed up that join, and explain in one sentence why customer_id — rather
than, say, total — is the right column to index for that use case.
✅ Quick Check
What's the main cost of adding an index to a frequently-updated column?
Next up: what happens when a single logical operation — like transferring money between two accounts — needs multiple SQL statements to succeed or fail together? That's what transactions are for.