data/query-optimization

query optimization Shipped

What this lens looks for

Run EXPLAIN QUERY PLAN on any new query touching large tables and flag the planner red flags: SCAN (full table scan that needs an index); USE TEMP B-TREE FOR ORDER BY (missing index on the ORDER BY columns); AUTOMATIC INDEX (SQLite built a throwaway index, so a permanent one is needed); CORRELATED SCALAR SUBQUERY (re-executes once per outer row — rewrite as a JOIN); and MATERIALIZE (a CTE got materialized where a subquery would have allowed index use). Beyond the plan, flag these query anti-patterns: correlated subqueries in SELECT (rewrite as JOINs); functions applied to indexed columns in WHERE such as WHERE date(col) = '...', which defeats the index — use a range comparison instead; UNION where UNION ALL would suffice, since the unneeded deduplication sort runs 60%+ slower; SELECT *, which blocks covering-index optimization — select only the needed columns; NOT IN against a subquery, which returns an empty result if the subquery yields any NULL — use NOT EXISTS instead; and OR across columns that aren't both indexed, which forces a full scan unless an index exists on each side.

What its verifier checks

  • EXPLAIN QUERY PLAN was run on every new query touching large tables; no SCAN (full table scan) left unaddressed by an index
  • No USE TEMP B-TREE FOR ORDER BY in the plan — ORDER BY columns are indexed
  • No AUTOMATIC INDEX in the plan — a permanent index is added where SQLite would otherwise build a temporary one
  • No CORRELATED SCALAR SUBQUERY in the plan — such subqueries are rewritten as JOINs
  • No MATERIALIZE where a subquery would permit index use instead of materializing a CTE
  • No correlated subqueries in SELECT where a JOIN would serve
  • No functions applied to indexed columns in WHERE (e.g., WHERE date(col) = '...'); range comparisons are used instead
  • UNION ALL is used in place of UNION wherever deduplication is not required
  • No SELECT *; queries select only the needed columns so covering indexes can apply
  • No NOT IN with a subquery that may return NULL; NOT EXISTS is used instead
  • No OR across columns unless both sides are indexed