skip to content

Indexes & Query Planner

SQLite's planner chooses between a SCAN and a SEARCH using ANALYZE statistics. Interviewers ask you to read EXPLAIN QUERY PLAN and spot the query that scans.

on this pageshow

explore

questions

6

In SQLite, what do ANALYZE and the sqlite_stat1 table give the query planner, and how should an app run PRAGMA optimize?

level: middleimportance: should knowfreq 42%

basics

~20 s

ANALYZE scans each index and stores row counts and average duplicates per key prefix in sqlite_stat1, which the planner uses to choose indexes and join order. Statistics never refresh by themselves, so apps run PRAGMA optimize, which re-analyzes only tables that need it.

open as a page

In SQLite's EXPLAIN QUERY PLAN, what do USING COVERING INDEX and USE TEMP B-TREE FOR ORDER BY mean, and how do you avoid the sort?

level: middleimportance: should knowfreq 48%

basics

~20 s

USING COVERING INDEX means SQLite read only the index, never the table rows. USE TEMP B-TREE FOR ORDER BY means it sorted the result in a temporary B-tree. An index on the equality-filter columns followed by the ORDER BY columns removes that sort.

open as a page

In SQLite, a partial index on live contacts and an expression index on lower(surname) are both ignored; what matching rules must the query satisfy?

level: seniorimportance: should knowfreq 36%

basics

~20 s

SQLite uses a partial index only when a query WHERE term exactly matches an index WHERE term, or implies its IS NOT NULL. An expression index needs the indexed expression, as written, in the WHERE or ORDER BY. SQLite does no algebra.

open as a page

In SQLite, when does declaring a table WITHOUT ROWID speed up lookups, and what do you give up by doing it?

level: middleimportance: nice to knowfreq 24%

basics

~20 s

WITHOUT ROWID stores a table as one B-tree keyed by its primary key, so a lookup on a non-integer or composite key takes one search instead of two. It suits small rows and costs AUTOINCREMENT, rowid functions and the update hook.

open as a page

In SQLite, a join of contacts c and call_log l shows `SEARCH l USING AUTOMATIC COVERING INDEX (phone=?)`; what does that mean, and what should you change?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

SQLite found no index on call_log.phone, so for this one statement it builds a transient index, uses it for the join, and discards it. The plan works but pays the build cost on every run; a persistent index on call_log(phone) is the fix.

open as a page