Query Performance

Read the plan before changing anything. EXPLAIN shows what the database intends to do; EXPLAIN ANALYZE runs it and shows what actually happened, including where the row estimates were wrong. Guessing at optimisations without a plan is how people add indexes that go unused.

Sargability is the property that lets an index be used. Wrapping an indexed column in a function destroys it: WHERE YEAR(created_at) = 2026 must compute YEAR() for every row, so it scans. WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' expresses the same thing as a range the index can seek. The same applies to leading wildcards — LIKE '%term' cannot use a B-tree, because there is no prefix to seek on.

Leftmost prefix. A composite index on (a, b) serves queries filtering on a, or on a and b — but not b alone, because the index is sorted by a first. Column order in a composite index is a design decision, not a formality.

Covering indexes include every column the query needs, so the engine answers from the index without touching the table. Often a large win on wide tables, paid for in index size and write cost.

SELECT \* is not free. It transfers columns you do not use, prevents index-only scans, and breaks silently when the schema changes.

Watch for implicit conversions. Comparing a string column to a number, or joining columns with different types or collations, can quietly disable index use — the plan will show a scan where you expected a seek.

OFFSET gets slower the deeper you go, because the engine still walks and discards every skipped row. Keyset pagination — "where id > last_seen_id" — stays fast at any depth.

Estimates matter. If the plan expects 10 rows and gets 100,000, the join strategy it chose is probably wrong, and the fix is usually fresher statistics rather than a hint.