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 the two apart are not complicated. You index the columns that your queries filter, join and sort on, and in a composite index you put them in the order that the engine will use them.
The previous lesson in this module covered "How Does EXPLAIN Reveal a Query's Plan?", and the next lesson in this module is about "Which Fixes Rescue the Most Common Slow Queries?".
Which columns deserve an index?
There are five kinds of column that earn an index, and the list is short enough to memorise.
- The foreign key columns. Any column that points at the primary key of another table is a join key, and the joins are where the row multiplication happens.
- The columns used in the WHERE filters, and especially those with many distinct values.
- The columns used in the ORDER BY, when the query is frequent and the sort is large.
- The columns used in the GROUP BY, which removes the temp b-tree from the plan.
- The columns used for the lookups where the value is unique, like an email address.
The join-column case is the one that people forget.
Here is a lookup by the 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=?)It is 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, and on a streaming service that is one of the most frequent queries that there is.
What order should a composite index use?
The order inside a composite index is the order that the engine will use it, and the rule follows from the way that a search works. The equality first, then the range, and then the sort.
The equality column needs an index of its own before the composite one can be judged, so here is the index on the movie_id.
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);An index on the pair of movie_id and minutes_watched serves a query that pins the movie_id and then compares the 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 the movie_id alone, and then it filters on the minutes_watched as it goes.
To have the index serve both conditions, both columns have to 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. It is 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 the pair of minutes_watched and movie_id cannot be used to search for a movie_id without first narrowing on the minutes_watched, and that is not what the query does.
Which columns make the 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 for every 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 24There are two distinct values across the whole table.
An index on completed tells the engine almost nothing that it did not already know, and it costs a second copy of the column.
The general rule is that 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?
The index catalogues grow, and the practical question is which of them are still earning their keep.
This schema carries seven of them. There are two join indexes, the covering index, the composite on the pair of movie_id and watched_on, and three single-column indexes on the columns that the reports filter and sort by.
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_eventsThere are seven indexes, and reading the list tells you something immediately. The ix_viewing_events_movie is now redundant, because the ix_viewing_events_movie_date and the ix_viewing_events_cover both begin with the movie_id, and they can serve every lookup that the single-column index served.
A composite index with the same leading column supersedes the single-column one.
SQLite has its own ANALYZE and the sqlite3_analyzer tool. PostgreSQL has the pg_stat_user_indexes view with its idx_scan counter, and MySQL has the performance_schema. All of them exist to answer the same question in a large database. Which of the indexes have never been used, and which of them can be dropped?
Quick reference
- Index the foreign keys, the filter columns, the sort columns and the group columns. Those are the four that pay.
- In a composite index you put the equality column first, then the range column, and then the sort column.
- A covering index answers the whole query without touching the table, and it is the cheapest win that is available.
- Low-cardinality columns make the poor standalone indexes. Two distinct values tell the engine nothing.
- A composite index supersedes a single-column index on its leading column, so you review the catalogue for the redundancy.
Try it yourself: for the query "which movies did the premium users start but not finish, in March, ordered by the start date", list the columns that an index should contain, and put them in the order that you would declare them. Then say which one of the four would be first, and why.