Reading a Query Plan

The database will tell you how it intends to answer a query. Ask it, rather than guessing.

EXPLAIN QUERY PLAN in SQLite; EXPLAIN and EXPLAIN ANALYZE in PostgreSQL and MySQL. The vocabulary differs and the idea is the same: what is it reading, and is it using an index?

A SCAN of a large table in a query that runs often is the thing to fix. That is the whole skill in one sentence.

Three ways to accidentally disable an index.

Wrapping the column in a function: WHERE lower(name) = 'x' cannot use a plain index on name, because the index holds the original values.

A leading wildcard: LIKE '%son' cannot use one either, for the same reason a book index cannot find words by their ending.

Comparing different types, which forces a conversion on every row.

Column order matters in a multi-column index. An index on (year, title) helps a query filtering on year, or on year and title, and does nothing for one filtering on title alone.

✦ This is the same lesson as the React performance module

Measure first. An index added on a guess is code with a cost and no proven benefit, and someone has to maintain it. Run the plan, find the SCAN, add the index, run the plan again and confirm it changed. Three steps, and the last one is the one people skip.