Most slow queries are slow for one of a handful of reasons, and the reasons repeat. There is a function that is wrapped around an indexed column. There is an OR where two indexed lookups would do. There is a subquery that scans when it could search. There is a filter that is applied after the join instead of before it. Each one has a standard repair, and once you can name the pattern in a plan you can fix it in minutes.
The previous lesson in this module covered "Which Columns Deserve an Index?", and this lesson is the last one in the Views, Indexes and Performance module.
Which fixes rescue the most common slow queries?
There are four repairs that cover the large majority of the cases, and each one has a name that you can recognise in a plan.
Fix one: stop wrapping the indexed column in a function
An index stores the values of a column. The moment that you ask for the DATE(watched_on), the engine is being asked to compare a computed expression against an index of the raw values, and no index can help.
You index the date column, and the same query is still a scan.
CREATE INDEX ix_viewing_events_date ON viewing_events(watched_on);
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE DATE(watched_on) = '2025-03-14';QUERY PLAN
`--SCAN viewing_events USING COVERING INDEX ix_viewing_events_dateYou rewrite the condition so that the column appears bare, and the same index becomes a range search.
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE watched_on >= '2025-03-14' AND watched_on < '2025-03-15';QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_date (watched_on>? AND watched_on<?)It is a SEARCH with two bounds.
The fix generalises. A range comparison against the bare column is always indexable, and it is equivalent to the function form as long as the data is stored as the sortable text, which is another argument for the ISO date format from module 03.
Fix two: replace the OR with two indexed lookups
An OR across two columns is awkward for an index, because each branch may match a different part of the structure.
The index that both branches can use is the one on the movie_id.
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);With it in place, the plan reads this.
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE movie_id = 5 OR movie_id = 9;QUERY PLAN
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_movie (movie_id=?)The optimiser of SQLite rewrote this into a single index search here, which is the best possible outcome. But that optimisation is not guaranteed across the engines, and it cannot help when the two branches touch different columns.
The form that always works is a UNION ALL of the two searches.
EXPLAIN QUERY PLAN
SELECT event_id FROM viewing_events WHERE movie_id = 5
UNION ALL
SELECT event_id FROM viewing_events WHERE movie_id = 9;QUERY PLAN
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| `--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_movie (movie_id=?)
`--UNION ALL
`--SEARCH viewing_events USING COVERING INDEX ix_viewing_events_movie (movie_id=?)There are two searches, each one using the index, with no branch able to fall back to a full scan.
It is the UNION ALL and not the UNION, because the UNION would deduplicate and sort the results. That is work that you do not need when the two branches are guaranteed to be disjoint.
Fix three: prefer the EXISTS to the IN
A membership test that is written with the IN usually makes the engine build the whole set of the matches before it can answer the question.
EXPLAIN QUERY PLAN
SELECT u.name FROM users u
WHERE u.user_id IN (SELECT v.user_id FROM viewing_events v WHERE v.completed = 1);QUERY PLAN
|--SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
`--LIST SUBQUERY 1
`--SCAN vThe LIST SUBQUERY scans the events table in full, it builds a list of every matching user_id, and then it tests each user against it.
The EXISTS turns the question around. For each user, you look for the one matching event, and you stop at the first.
The inner lookup needs the index on the user_id to be a search and not a scan.
CREATE INDEX ix_viewing_events_user ON viewing_events(user_id);With that index in place, the plan reads this.
EXPLAIN QUERY PLAN
SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM viewing_events v WHERE v.user_id = u.user_id AND v.completed = 1);QUERY PLAN
|--SCAN u
`--CORRELATED SCALAR SUBQUERY 1
`--SEARCH v USING INDEX ix_viewing_events_user (user_id=?)The inner scan became an index search, and the search stops at the first match.
Both forms return the same answer, which is the seven users either way. But the second one never materialises a list, and it never scans the whole of the events table.
Fix four: filter before you join
This one is about the order of the work rather than about an index.
A query that filters the events table and then joins looks like this.
EXPLAIN QUERY PLAN
SELECT m.title
FROM viewing_events v JOIN movies m ON m.movie_id = v.movie_id
WHERE v.completed = 1 AND v.minutes_watched > 60;QUERY PLAN
|--SCAN v
`--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)The plan scans the events table, and only then does it look up each matching movie by the primary key.
You add an index that lets the "watched for more than an hour" filter be found directly, and the scan narrows to the matching rows.
CREATE INDEX ix_viewing_events_done_minutes ON viewing_events(minutes_watched, completed);
EXPLAIN QUERY PLAN
SELECT m.title
FROM viewing_events v JOIN movies m ON m.movie_id = v.movie_id
WHERE v.completed = 1 AND v.minutes_watched > 60;QUERY PLAN
|--SEARCH v USING INDEX ix_viewing_events_done_minutes (minutes_watched>?)
`--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)The SCAN became a SEARCH, and the index is not a covering one because the query needs the movie_id, which the index does not contain.
The general principle is the one from module 04. You reduce the number of rows as early as possible, because every row that you eliminate is a row that the rest of the plan never has to touch.
What are the other repairs worth knowing?
- Select the named columns and not the
*. A covering index can answer a query that lists three columns without touching the table. The same query with theSELECT *always has to fetch the whole row. - Never count the rows to test for the existence. A
COUNTover a large table reads everything, and theEXISTSstops at the first row. - Use the smallest type that fits. A table of
INTkeys is smaller, and the smaller tables are faster to scan, to cache and to sort. The index work is included in that. - Keep the statistics fresh. The
ANALYZEof PostgreSQL, theANALYZE TABLEof MySQL and theANALYZEof SQLite let the optimiser choose sensibly. The stale statistics produce the bad plans. - Check the plan after every change. An index that does not change the plan is pure cost, and you will not notice it without looking.
Quick reference
- You never wrap an indexed column in a function. You rewrite the predicate as a range on the bare column.
- The
UNION ALLof two indexed branches beats an OR that the optimiser cannot satisfy. - The
EXISTSstops at the first match, and theINbuilds the whole list first. - You filter early. Every row that is eliminated before the join is a row that the join never touches.
- You use the
SELECTwith the named columns, so that a covering index can answer the query outright.
Try it yourself: take your slowest query and rewrite it three times. Once with the function removed, once with the OR split into the UNION ALL, and once with the EXISTS instead of the IN. Run the EXPLAIN QUERY PLAN on each of them and report which line changed, because the line that changed is the line that was costing you.