An index is a second, sorted copy of the data in a table, and it is built for one purpose. It lets the engine find the handful of rows it needs without reading all of them. It is the closest thing that SQL has to a performance dial, and it is also the only optimisation that costs something on every write.
The previous lesson in this module covered "What Are Views and When Do They Earn Their Keep?", and the next lesson in this module is about "How Does EXPLAIN Reveal a Query's Plan?".
Indexes make reads dramatically faster and they make writes slightly slower, and the craft is in choosing where to spend that trade.
How do the indexes speed the reads and slow the writes?
You 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=?)It is a SEARCH and not a SCAN. The engine jumped straight to the row.
Now you 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_eventsA 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 rows 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 that holds the values of the indexed column, 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 that it needs.
Because the structure is smaller than the table, because it holds the values and the identifiers rather than the whole rows, more of it fits in memory. A search that would have touched the disk may now touch only the RAM.
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);You 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=?)It is a SEARCH and not a SCAN. And notice 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 that the query wants, so the engine never has to visit the table itself at all. A covering index turns a lookup-and-fetch into a single lookup.
The index is now part of the schema, and it is 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_eventsWhat do the indexes cost?
- Every insert and update has to update the index too. An index on a frequently updated column means that every update writes to two structures. On a write-heavy table, the indexes are the reason that a write slows down.
- Storage. Each index is a complete second copy of the indexed data plus the 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 the read speed with the write speed, the storage, and the planning time.
Which means that the question is never "should I add an index". It is "which query does this index make fast, and is that query worth the write cost".
What is a composite index?
It is an index over more than one column, where the columns are sorted in the order that they are declared.
You declare one on the pair of movie_id and watched_on, and the engine can serve both the "all events for the movie 5" and the "all events for the 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 the reason that 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 the reason that 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=?)It is one structure that does three jobs. The equality filter, the range filter, and the ordering. That efficiency is the reason that a well-chosen composite index is worth several single-column indexes.
Quick reference
- A SCAN reads every row, and a SEARCH uses an index to jump to the rows that it needs.
- An index is a sorted copy of one or more columns plus the row identifier.
- A covering index supplies every column that the query wants, so the table is never visited.
- Indexes make the reads fast and the writes slower. They are the storage and the update cost that is paid for the speed.
- A composite index serves the filters and the sorts together, which is the reason that the column order inside it matters.
Try it yourself: 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 has to come first in the declaration, and why.