Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 33 ยท Performance

EXPLAIN QUERY PLAN and the Optimizer

Read SQLite's EXPLAIN QUERY PLAN output, tell SCAN from SEARCH, use ANALYZE and PRAGMA optimize, and fix slow queries with a repeatable routine.

When a query is slow, guessing is a bad strategy. SQLite will tell you exactly how it intends to run a statement if you put EXPLAIN QUERY PLAN in front of it. The output is a short outline: which tables are read, in what order, with which index, and whether a temporary sort is needed. After a little practice you can read it in seconds, and it turns "this feels slow" into a concrete fix.

This lesson teaches the vocabulary of the plan, the most common reasons an index is not used, and how the optimizer's statistics (ANALYZE, PRAGMA optimize) fit in. It builds on Indexes, so read that first if CREATE INDEX is new to you.

A tuning loop that works

Find the slow query Measure first, don't guess.
Read the plan EXPLAIN QUERY PLAN in front.
Fix one thing Add an index or rewrite the filter.
Check again The plan should change.
1 / 2

Reading a plan

Add EXPLAIN QUERY PLAN before any SELECT, UPDATE or DELETE. The result has four columns (id, parent, notused, detail) and one row per step. The only column you need is detail; the other three are internal (the id values can change between runs, and parent shows nesting). The most common words in detail:

You read It means
SCAN t Every row of t is read, in rowid order
SCAN t USING INDEX i The whole index is walked (often to avoid sorting)
SEARCH t USING INDEX i (col=?) The index is used to jump to rows matching the condition in parentheses
SEARCH t USING INTEGER PRIMARY KEY (rowid=?) A direct lookup by rowid / INTEGER PRIMARY KEY
... USING COVERING INDEX i The index alone holds every needed column
USE TEMP B-TREE FOR ORDER BY SQLite sorts the rows with a temporary structure
USE TEMP B-TREE FOR GROUP BY / DISTINCT The same, for grouping or de-duplication

The ? in (col=?) is just a placeholder for the value being searched. What matters is which columns appear there, because those are the parts of your WHERE that the index actually narrows.

SQL Four plans side by side

Press Run (or Ctrl + Enter) to see the result here.

You should see SEARCH orders USING INTEGER PRIMARY KEY (rowid=?) for the first, SEARCH orders USING INDEX idx_customer (customer_id=?) for the second, SCAN orders for the third, and for the fourth the same SEARCH followed by USE TEMP B-TREE FOR ORDER BY. A SCAN is not automatically wrong (small tables, or queries that really need every row), but on a big table it is the first thing to look for in a slow query.

Joins: one loop inside another

For a join SQLite runs nested loops: an outer table is read, and for each row the inner table is looked up. The plan lists the tables in loop order, outermost first. A healthy join usually ends with a SEARCH on the inner table, ideally on an index or primary key.

SQL A join and an automatic index

Press Run (or Ctrl + Enter) to see the result here.

The first plan reads SEARCH o USING INDEX idx_orders_status (status=?) and then SEARCH c USING INTEGER PRIMARY KEY (rowid=?). The second shows SEARCH b USING AUTOMATIC COVERING INDEX (ref=?). An automatic index is a temporary index that SQLite builds for the duration of one query because the join column had none. It is a clever rescue, but also a hint: if a query keeps building one, create a real index on that column and every run saves the build time.

Why was my index ignored?

Most "my index is not used" problems come from a few patterns. In each case the filter is written so that SQLite cannot compare the stored column value directly.

SQL Functions on columns

Press Run (or Ctrl + Enter) to see the result here.

The strftime and n + 1 filters give SCAN events; their rewrites give SEARCH events USING INDEX ... (created>? AND created<?) and (n=?). The final query shows both month filters count the same rows (310), so the rewrite changes only speed. The fix is almost always "move the computation to the other side of the comparison", or, when you cannot, create an expression index.

More patterns that prevent index use:

  • A leading wildcard: name LIKE '%son' cannot use an ordered index because matches are not contiguous.
  • Case rules with LIKE: LIKE is case-insensitive in SQLite, but an ordinary index sorts case-sensitively, so LIKE 'abc%' does not use it. The case-sensitive GLOB 'abc*' does, or declare the column COLLATE NOCASE and the LIKE can.
  • Skipping the leftmost column of a composite index.
  • OR across different columns: SQLite can combine two indexes with a MULTI-INDEX OR, but only when both sides have one; otherwise rewrite as UNION.
SQL LIKE, GLOB and OR

Press Run (or Ctrl + Enter) to see the result here.

LIKE 'name12%' and LIKE '%12' both read SCAN people, while the GLOB version reads SEARCH people USING INDEX idx_people_name (name>? AND name<?). The first OR query scans. After the second index exists the plan becomes MULTI-INDEX OR with one SEARCH per index, one for name=? and one for city=?.

Statistics: ANALYZE and PRAGMA optimize

When several indexes could serve a query, SQLite picks by estimating how many rows each would return. By default it guesses from the index shape alone. The ANALYZE command measures the data and stores summaries in the table sqlite_stat1, which later plans read. Each row has the table, the index, and a stat string: the first number is the total rows in the index, the next numbers are the average number of rows that share the same value in the first column, the first two columns, and so on.

SQL What ANALYZE records

Press Run (or Ctrl + Enter) to see the result here.

You get idx_logs_level 4000 2000 (4,000 rows; each level value matches 2,000 rows on average), idx_logs_user 4000 10 (400 users, 10 rows each) and idx_logs_user_level 4000 10 10 (adding level does not narrow things further, because every user always has the same level). With numbers like these SQLite knows that user_id is a far better filter than level.

Two caveats are worth remembering. First, statistics are averages, so skewed data can still fool the planner:

SQL Skew defeats averages

Press Run (or Ctrl + Enter) to see the result here.

There are 4,995 common rows and 5 rare ones, yet sqlite_stat1 says 5000 2500: the average of the two. SQLite cannot tell the two values apart, so it uses the index for 'common' as well and walks nearly the whole table through it, which is slower than a plain scan. A leading + before a column (+kind = 'common') makes SQLite treat the term as a plain expression and not use an index for it, giving SCAN tickets. NOT INDEXED after the table name does the same for a whole table reference, and INDEXED BY idx forces one. These overrides are tools of last resort; they hard-code a decision that may be wrong when the data changes.

Second, statistics go stale when your data changes a lot. Rather than scheduling ANALYZE by hand, run PRAGMA optimize. It looks at what the connection did and re-analyzes only tables whose statistics might be out of date. The recommended habit is to run it just before closing each long-lived connection, and every few hours on connections that stay open. Short-lived connections can run it once at the end; a connection that stays open for days should run it periodically.

SQL PRAGMA optimize picks up new indexes

Press Run (or Ctrl + Enter) to see the result here.

After ANALYZE only idx_a has a statistics row. The new index idx_b has none until PRAGMA optimize notices it and analyzes it, so the second listing has both. (On a real file database you would run PRAGMA optimize and then forget about it; the numbers it writes may come from a sample, so do not compare them digit for digit with a full ANALYZE.)

A routine for fixing a slow query

  1. Find a query worth fixing. Log timings in your application; a query run 10,000 times a minute matters more than a slow report run once a week.
  2. Run it under EXPLAIN QUERY PLAN with realistic data. An empty or tiny table gives plans that are not representative.
  3. Look for SCAN on a large table, TEMP B-TREE steps and AUTOMATIC indexes.
  4. Pick the cheapest fix: rewrite the filter so the bare column is compared, then add or reshape an index (equality columns first, then range or ORDER BY), then consider a covering or partial index.
  5. Check the plan again, then time it again. If the plan did not change, the fix did nothing.
  6. Run PRAGMA optimize and keep an eye on the cost of the extra index on writes.

The plan shows what SQLite intends to do, not how long it takes. Real timing and a measure of rows read, in your own application, is what finally tells you whether a change helped.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A plan line reads SEARCH orders USING INDEX idx_customer (customer_id=?). What does it mean?

    SEARCH means an index (or the primary key) narrows the rows, and the columns in parentheses are the ones used for that narrowing.
  2. 2. Which rewrite lets WHERE strftime('%Y', created) = '2025' use an index on created?

    Comparing the bare column to a range lets SQLite search the sorted index. A function wrapped around the column hides it, and leading wildcards or a unary plus prevent index use as well.
  3. 3. What is an AUTOMATIC COVERING INDEX in a plan?

    It is a rescue that costs time on every run, and a hint to create a real index on that join column.
  4. 4. What does sqlite_stat1 contain after ANALYZE?

    The planner reads these averages to estimate how selective each index is, which is why skewed data can still fool it.
  5. 5. Why is PRAGMA optimize preferred over calling ANALYZE on a schedule?

    PRAGMA optimize is cheap because it decides what actually needs analyzing, so it can run often, for example before closing a connection.

Summary

  • EXPLAIN QUERY PLAN shows the strategy; read the detail column. SCAN visits all rows, SEARCH ... USING INDEX jumps to matches, COVERING INDEX avoids the table, and USE TEMP B-TREE means a sort or grouping step.
  • Indexes are ignored when the column is wrapped in a function or arithmetic, when LIKE meets a case-sensitive index or a leading wildcard, or when a composite index's leftmost column is skipped.
  • ANALYZE stores averages in sqlite_stat1; skewed data can still fool them, and +column, NOT INDEXED and INDEXED BY are last-resort overrides.
  • Run PRAGMA optimize regularly (and before closing long-lived connections) instead of managing ANALYZE by hand.
  • Fix slow queries in a loop: measure, read the plan, change one thing, read the plan again.

Next: PRAGMAs and Tuning covers the settings that change how the whole database engine behaves.