SQL Indexes
A separate, sorted lookup structure that points back to the real rows — like a book's index, not the book itself.
What & Why
An index is extra data the database maintains alongside a table — sorted by one or more columns — so it can find matching rows without scanning the whole table. It costs extra disk space and slows down writes slightly (the index must update too), in exchange for much faster reads on the indexed column.
See How It Works
BUSINESS QUESTION
A selective user lookup filters by one stable key and returns only the requested row.
| id | created_at | country | channel | plan | activated_at | churned_at |
|---|---|---|---|---|---|---|
| 101 | 2024-01-15 09:10:00+00 | US | organic | pro | 2024-01-16 14:25:00+00 | NULL |
| 102 | 2024-02-10 11:05:00+00 | CA | paid | free | NULL | 2024-03-20 10:00:00+00 |
| 103 | 2024-03-12 16:35:00+00 | GB | referral | pro | 2024-03-13 08:15:00+00 | NULL |
| 104 | 2024-04-01 13:20:00+00 | US | organic | free | 2024-04-03 12:00:00+00 | NULL |
EXAMPLE QUERY
SELECT
id AS user_id,
country,
plan
FROM growth.users
WHERE id = 101;RESULT — primary-key lookup
| user_id | country | plan |
|---|---|---|
| 101 | US | pro |
The representative user with ID 101 is returned.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario