An index is a second, sorted copy of a table's data built for one purpose: to let the engine find the handful of rows it needs without reading them all. It is the closest thing SQL has to a performance dial, and it is also the only optimisation that costs something on every write. Indexes make reads dramatically faster and make writes slightly slower, and the craft is in choosing where to spend that trade.
How do indexes speed reads and slow writes?
Look up a row by its primary key and the engine already has what it needs, because a rowid table is itself a sorted structure:
EXPLAIN QUERY PLAN
SELECT title FROM movies WHERE movie_id = 3;
QUERY PLAN
`--SEARCH movies USING INTEGER PRIMARY KEY (rowid=?)
SEARCH, not SCAN — the engine jumped straight to the row. Now ask a question about a column that has no index at all:
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE movie_id = 5;
QUERY PLAN
`--SCAN viewing_events
A SCAN is the engine reading every row of the table and checking each one. With twenty-four rows that is instant; with twenty-four million it is the difference between a dashboard that loads and one that times out.
What does an index actually do?
It builds a second structure containing the indexed column's values, sorted, plus the row identifier for each one. That is all an index is — and it explains everything about how it behaves. Because the values are sorted, the engine can seek to the right part of the structure and read only the entries it needs. Because the structure is smaller than the table (it holds values and identifiers rather than whole rows), more of it fits in memory, and a search that would have touched the disk may now touch only RAM.
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);
Run the same query again and the plan has changed:
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE movie_id = 5;
QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_movie (movie_id=?)
SEARCH instead of SCAN. And note the words COVERING INDEX: the index contains both the movie_id and the row identifier, and the row identifier is enough to locate the event_id the query wants, so the engine never has to visit the table itself at all. A covering index turns a lookup-plus-fetch into a single lookup. The index is now part of the schema, visible like any other object:
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'viewing_events';
name tbl_name
----------------------- --------------
ix_viewing_events_movie viewing_events
What do indexes cost?
Three things, and the first is the one that bites.
- Every insert and update has to update the index too. An index on a frequently updated column means every update writes to two structures. On a write-heavy table, indexes are the reason a write slows down.
- Storage. Each index is a complete second copy of the indexed data plus identifiers. On a wide table, a dozen indexes can outweigh the table itself.
- Planning time. More indexes means more candidates for the optimiser to evaluate, and on a complex query with many tables that planning cost can become visible.
That is the trade in one sentence: an index buys read speed with write speed, storage and planning time. Which means the question is never "should I add an index" but "which query does this index make fast, and is that query worth the write cost".
What is a composite index?
An index over more than one column, where the columns are sorted in the order they are declared. Declare one on (movie_id, watched_on) and the engine can serve both "all events for movie 5" and "all events for movie 5 in a date range":
CREATE INDEX ix_viewing_events_movie_date ON viewing_events(movie_id, watched_on);
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE movie_id = 5 AND watched_on >= '2025-03-14';
QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_movie_date (movie_id=? AND watched_on>?)
Both columns are used in the search, which is why the order in the declaration matters so much — lesson 52 works through that in detail. The same index also answers the sort without a separate sorting step, which is why the plan for an ordered query still says SEARCH rather than adding a temp b-tree:
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE movie_id = 5 ORDER BY watched_on;
QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_movie_date (movie_id=?)
One structure, doing three jobs: the equality filter, the range filter and the ordering. That efficiency is why a well-chosen composite index is worth several single-column indexes.
Quick reference
- SCAN reads every row; SEARCH uses an index to jump to the rows it needs.
- An index is a sorted copy of one or more columns plus the row identifier.
- A covering index supplies every column the query wants, so the table is never visited.
- Indexes make reads fast and writes slower — they are storage and update cost paid for speed.
- A composite index serves filters and sorts together, which is why column order inside it matters.
Index drill: the dashboard shows, for each movie, the users who watched it in a chosen month. Write the composite index that serves that screen, and say which column must come first in the declaration and why.