Indexes

An index lets the database find rows by a column without scanning the table. Add them for the columns you filter, join and sort on, and check with EXPLAIN that queries actually use them.

What a session covers

The tutor works through these with you, in conversation and code. A session ends when you have shown them, not when a video does.

Explain how an index finds rows without scanning the table
Add an index for a filtered and sorted query and check it with EXPLAIN
Spot the table indexed on every column and explain the write cost

In practice

A few lines that show Indexes doing its job.

SQL

CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 'c1a2...' ORDER BY created_at DESC LIMIT 20;
-- Index Scan using orders_customer_created_idx ... (actual time=0.04..0.09 rows=20)

The common mistake

Watch for

Indexing every column, or none. Each index slows writes and takes space; index what the query plans show is being scanned, and put the equality column first in a composite index.

What to know first

Your roadmap puts these before Indexes, unless you already know them.

SQL queries

How it stays learned

Once a session shows you understand Indexes, it comes back for review. Each review takes a minute and tells the memory model how well you still know it.

Day 3First recall, while it is fresh
Day 7Second, a week on
Day 18Last, when it has settled

Previous in the module← Schema design