Most slow queries are slow for one of a handful of reasons, and the reasons repeat. A function wrapped around an indexed column. An OR where two indexed lookups would do. A subquery that scans when it could search. A filter applied after the join instead of before it. Each has a standard repair, and once you can name the pattern in a plan you can fix it in minutes.
Which fixes rescue the most common slow queries?
Fix one: stop wrapping the indexed column in a function
An index stores the values of a column. The moment you ask for DATE(watched_on), the engine is being asked to compare a computed expression against an index of raw values, and no index can help. 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_date
Rewrite the condition so 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<?)
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 sortable text — which is another argument for the ISO date format from module 03.
Fix two: replace 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 both branches can use is the one on movie_id:
CREATE INDEX ix_viewing_events_movie ON viewing_events(movie_id);
With it in place, the plan reads:
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=?)
SQLite's optimiser rewrote this into a single index search here, which is the best possible outcome — but that optimisation is not guaranteed across engines, and it cannot help when the two branches touch different columns. The form that always works is a UNION ALL of 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=?)
Two searches, each using the index, with no branch able to fall back to a full scan. UNION ALL rather than UNION, because UNION would deduplicate and sort the results — work you do not need when the two branches are guaranteed disjoint.
Fix three: prefer EXISTS to IN
A membership test written with IN usually makes the engine build the whole set of 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 v
The LIST SUBQUERY scans the events table in full, builds a list of every matching user_id, and then tests each user against it. EXISTS turns the question around — for each user, look for one matching event and stop at the first. The inner lookup needs the index on user_id to be a search rather than a scan:
CREATE INDEX ix_viewing_events_user ON viewing_events(user_id);
With that index in place, the plan reads:
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. The two forms return the same answer — seven users either way — but the second one never materialises a list and never scans the whole events table.
Fix four: filter before you join
This one is about the order of work rather than 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 looks up each matching movie by primary key. 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 movie_id, which the index does not contain. The general principle is the one from module 04: reduce the number of rows as early as possible, because every row you eliminate is a row the rest of the plan never has to touch.
What are the other repairs worth knowing?
- Select named columns, not *. A covering index can answer a query that lists three columns without touching the table; the same query with SELECT * always has to fetch the whole row.
- Never count rows to test for existence. A COUNT over a large table reads everything; EXISTS stops at the first row.
- Use the smallest type that fits. A table of INT keys is smaller, and smaller tables are faster to scan, cache and sort — index work included.
- Keep statistics fresh. PostgreSQL's ANALYZE, MySQL's ANALYZE TABLE and SQLite's ANALYZE let the optimiser choose sensibly; stale statistics produce bad plans.
- Check the plan after every change. An index that does not change the plan is pure cost, and you will not notice without looking.
Quick reference
- Never wrap an indexed column in a function — rewrite the predicate as a range on the bare column.
- UNION ALL of two indexed branches beats an OR that the optimiser cannot satisfy.
- EXISTS stops at the first match; IN builds the whole list first.
- Filter early: every row eliminated before the join is a row the join never touches.
- Use SELECT with named columns so a covering index can answer the query outright.
Repair drill: take your slowest query and rewrite it three times — once with the function removed, once with the OR split into UNION ALL, once with EXISTS instead of IN. Run EXPLAIN QUERY PLAN on each and report which line changed, because the line that changed is the line that was costing you.