Lesson 17 of 20
In progress
Indexes: making lookups fast
Understand what an index stores, choose the right columns and order, read a query plan, and know when an index is wasted.
Without an index, finding a row means reading the whole table. With one, the engine jumps to the right place. That is the whole idea, and the craft is in choosing which columns.
What you will learn
- Read an index as a sorted lookup structure.
- Order composite indexes to match queries.
- Check a plan and decide whether an index helps.
Seeing the difference
Editable. Runs in your browser.
INSERT INTO events (kind, actor, created_on)
SELECT
CASE WHEN value % 3 = 0 THEN 'login'
WHEN value % 3 = 1 THEN 'view'
ELSE 'edit' END,
'user' || (value % 50),
date('2025-01-01', '+' || (value % 90) || ' days')
FROM (
WITH RECURSIVE seq(value) AS (
SELECT 1 UNION ALL SELECT value + 1 FROM seq WHERE value < 500
)
SELECT value FROM seq
);
CREATE INDEX idx_events_kind ON events (kind);
EXPLAIN QUERY PLAN
SELECT actor, created_on FROM events WHERE kind = 'login';
Press Run to see the output.
The plan says SEARCH events USING INDEX idx_events_kind (kind=?), which means the engine used the index instead of scanning. Without the index it would say SCAN events. Read plans with EXPLAIN QUERY PLAN in SQLite; other databases have EXPLAIN with their own output.
The plan is the only reliable answer to "is this index used". Guessing from the query shape is how people add indexes that never run.
What an index stores
An index is a sorted structure of the indexed columns plus a pointer to the row. Because it is sorted, the engine can binary-search it, and it can also read a range in order without a sort step.
That explains three things:
- Equality on the indexed column is fast.
- A range or
ORDER BYon it can avoid a sort. - An index on a column you never filter or sort by does nothing for reads.
Composite indexes and column order
Editable. Runs in your browser.
EXPLAIN QUERY PLAN
SELECT * FROM events WHERE actor = 'user7' AND created_on >= '2025-03-01';
Press Run to see the output.
A composite index is sorted by the first column, then the second within equal first values. That means:
- A query filtering on
actoralone can use it. - A query filtering on
created_onalone cannot, because the first column is not constrained. - A query filtering on both uses both parts.
Order the columns by how they are queried: equality columns first, then the range or sort column. The rule of thumb is "equality, then range, then everything you select".
Covering indexes
If the index contains every column a query needs, the engine can answer from the index and never touch the table.
Editable. Runs in your browser.
EXPLAIN QUERY PLAN
SELECT actor, created_on FROM events WHERE kind = 'view';
Press Run to see the output.
The plan may mention USING COVERING INDEX, which is the fastest case for a read-heavy report. The cost is a wider index, so cover the queries that matter rather than every query.
When indexes hurt
Every insert, update and delete must also update each index. A table with eight indexes writes nine structures per row. On a write-heavy table that is a real cost, and it is invisible until the write volume grows.
Indexes also take space and slow down bulk imports. A common pattern is to drop non-essential indexes before a large import and recreate them afterwards.
Low-selectivity columns are poor candidates on their own: an index on a boolean flag divides the table in two, and a scan of half the rows is barely better than a full scan. A composite index that starts with the flag and continues with a selective column is often useful instead.
What defeats an index
WHERE LOWER(name) = 'ana'unless there is an index on the expression.WHERE name LIKE '%ana', because the match can start anywhere.WHERE CAST(id AS TEXT) = '42', because the indexed value is not being compared.- A type mismatch that forces conversion, such as comparing a text column with a number.
The fix is usually to change the query rather than the index: store the value normalised, or add an expression index when the database supports one.
Indexes and uniqueness
A UNIQUE constraint creates an index automatically, which is why lookups by email are fast without an extra CREATE INDEX. The same is true for primary keys. Do not duplicate those indexes.
Adding an index for every column that appears in a WHERE clause. Each one slows writes, most go unused, and the plan for a multi-condition query may need a composite index instead.
Add an index that supports finding an actor’s events in a date range, then check the plan for a query using both conditions and one using only the date.
Show solution
Editable. Runs in your browser.
EXPLAIN QUERY PLAN
SELECT * FROM events WHERE actor = 'user3' AND created_on >= '2025-02-01';
EXPLAIN QUERY PLAN
SELECT * FROM events WHERE created_on >= '2025-02-01';
Press Run to see the output.
The first plan searches the index, because the leading column is constrained. The second scans, because created_on is the second column and cannot be used without a value for actor. A separate index on created_on would be needed for that query, and whether it is worth the write cost depends on how often it runs.
Recap
- An index is a sorted structure that turns a scan into a search, and it is only used when the query matches its shape.
- Composite indexes are ordered: equality columns first, then ranges and sorts.
- Every index costs write time and space, and expressions or leading wildcards can defeat it.
Next, transactions: grouping changes so a failure cannot leave half the work done.
How many indexes is too many?
Do indexes help COUNT(*)?
Progress is saved in this browser only. No account, no email.