An index on the wrong column is pure cost, and an index on the right column is a permanent speed-up. The rules for telling them apart are not complicated: index the columns your queries filter, join and sort on — and in a composite index, put them in the order the engine will use them.
Which columns deserve an index?
Five kinds of column earn an index, and the list is short enough to memorise.
- Foreign key columns. Any column that points at another table's primary key is a join key, and joins are where the row multiplication happens.
- Columns used in WHERE filters — especially those with many distinct values.
- Columns used in ORDER BY, when the query is frequent and the sort is large.
- Columns used in GROUP BY, which removes the temp b-tree from the plan.
- Columns used for lookups where the value is unique, like an email address.
The join-column case is the one people forget. Here is a lookup by user_id, with and without an index on it:
CREATE INDEX ix_viewing_events_user ON viewing_events(user_id);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM viewing_events WHERE user_id = 4;
QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_user (user_id=?)
A SEARCH, and a covering one — the count could be answered from the index alone. Without the index this was a full scan of the events table on every "how much has this user watched" request, which on a streaming service is one of the most frequent queries there is.
What order should a composite index use?
The order inside a composite index is the order the engine will use it, and the rule follows from how a search works. Equality first, then range, then sort. The equality column needs an index of its own before the composite one can be judged, so here is the index on movie_id:
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);
An index on (movie_id, minutes_watched) serves a query that pins movie_id and then compares minutes:
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM viewing_events WHERE movie_id = 5 AND minutes_watched > 60;
QUERY PLAN
`--SEARCH viewing_events USING INDEX ix_viewing_events_movie (movie_id=?)
The plan uses the index on movie_id alone, then filters on minutes_watched as it goes. To have the index serve both conditions, both columns must be in it, with the equality column first:
CREATE INDEX ix_viewing_events_cover ON viewing_events(movie_id, minutes_watched);
EXPLAIN QUERY PLAN
SELECT minutes_watched FROM viewing_events WHERE movie_id = 5;
QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_cover (movie_id=?)
Now the query is answered entirely from the index — the word COVERING again. Both the filter and the projected column are in the structure, so the table is never touched at all. Put the columns the other way round and the second column would be useless: a composite index on (minutes_watched, movie_id) cannot be used to search for a movie_id without first narrowing on minutes_watched, which is not what the query does.
Which columns make poor indexes?
A column with very few distinct values is a poor index, because the index is nearly as large as the table and the engine still has to walk many entries per lookup. The completed flag is the obvious candidate:
SELECT COUNT(DISTINCT completed) AS distinct_values, COUNT(*) AS rows FROM viewing_events;
distinct_values rows
--------------- ----
2 24
Two distinct values across the whole table. An index on completed tells the engine almost nothing it did not already know, and it costs a second copy of the column. The general rule: a good index column has many distinct values, usually close to the number of rows. A boolean, a status with three states or a country code rarely qualifies. When such a column is part of a composite index it can still earn its place — as the equality prefix of a compound key — but it rarely deserves an index of its own.
How do you keep track of what you have?
Index catalogues grow, and the practical question is which ones are still earning their keep. This schema carries seven of them: the two join indexes, the covering index, the composite on (movie_id, watched_on), and three single-column indexes on the columns the reports filter and sort by. Every engine can show you the list:
CREATE INDEX ix_viewing_events_movie_date ON viewing_events(movie_id, watched_on);
CREATE INDEX ix_movies_genre ON movies(genre);
CREATE INDEX ix_movies_release_year ON movies(release_year);
CREATE INDEX ix_users_city ON users(city);
Here is the catalogue:
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND name LIKE 'ix_%' ORDER BY name;
name tbl_name
---------------------------- --------------
ix_movies_genre movies
ix_movies_release_year movies
ix_users_city users
ix_viewing_events_cover viewing_events
ix_viewing_events_movie viewing_events
ix_viewing_events_movie_date viewing_events
ix_viewing_events_user viewing_events
Seven indexes, and reading the list tells you something immediately: ix_viewing_events_movie is now redundant, because ix_viewing_events_movie_date and ix_viewing_events_cover both begin with movie_id and can serve every lookup the single-column index served. A composite index with the same leading column supersedes the single-column one. SQLite's own ANALYZE and sqlite3_analyzer tool, PostgreSQL's pg_stat_user_indexes with its idx_scan counter, and MySQL's performance_schema all exist to answer the question in a large database: which indexes have never been used and can be dropped.
Quick reference
- Index foreign keys, filter columns, sort columns and group columns — those are the four that pay.
- In a composite index put the equality column first, then the range column, then the sort column.
- A covering index answers the whole query without touching the table, and is the cheapest win available.
- Low-cardinality columns make poor standalone indexes — two distinct values tell the engine nothing.
- A composite index supersedes a single-column index on its leading column; review the catalogue for redundancy.
Strategy drill: for the query "which movies did premium users start but not finish, in March, ordered by start date", list the columns an index should contain and put them in the order you would declare them — then say which of the four would be first and why.