How to use SQLite EXPLAIN QUERY PLAN

Put EXPLAIN QUERY PLAN before a SQLite statement to see the high-level work the planner chose. The detail records show table scans, index searches, join nesting, and temporary sorting or grouping work.

3 minute read
Examples run in SQLite

On this page

EXPLAIN and EXPLAIN QUERY PLAN are different

EXPLAIN QUERY PLAN shows a short tree describing how SQLite reads each table. EXPLAIN shows the individual instructions SQLite uses to execute the statement. Start with EXPLAIN QUERY PLAN to check for scans, index searches, or temporary sorting and grouping work.

SCAN describes a full scan of a table or index, while SEARCH describes a lookup that visits a subset of rows. A scan can be a suitable choice when the query needs most rows. Neither label measures elapsed time.

EXPLAIN QUERY PLAN
SELECT id, name
FROM customers
WHERE email = 'ada@example.com';

One small change, two different plans

The customers table has an index on email. The first query wraps that column in a function.

Schema and sample SQL

Schema and sample data

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT NOT NULL
);
CREATE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (id, email, name) VALUES
  (1, 'ada@example.com', 'Ada Lovelace'),
  (2, 'grace@example.com', 'Grace Hopper');

Before

SELECT id, name
FROM customers
WHERE lower(email) = 'ada@example.com';

Because of the function, SQLite can report a scan of every customer row.

SCAN customersReads rows by scanning a table or index.

Open in the Visualizer

After WHERE email = 'ada@example.com';

-- Replace lower(email) with email for the direct lookup.

SELECT id, name
FROM customers
WHERE email = 'ada@example.com';

If the stored email values already use the needed case, a direct match can use the index.

SEARCH customers USING INDEX idx_customers_email (email=?)Finds the matching rows through an index.

Open in the Visualizer

Both forms return the same row in the sample. The plans differ because the index stores email values rather than lower(email) values. Before changing an application query, check that the new comparison preserves its rules for uppercase and lowercase characters.

Key points

Find each SCAN or SEARCH record and name the table it reads.
For a search, note the index name and the terms in parentheses.
Look for USING COVERING INDEX when the query can avoid table lookups.
Look for USE TEMP B-TREE when the query sorts, groups, or removes duplicates.
For joins, read the nesting order and ask how many times each inner record may run.
Run the original query and protect its result before you change SQL or indexes.

Do not parse the text as a stable API

SQLite documents that the output format can change between releases. Use the plan for diagnosis and for tests that check behavior you own. Do not promise that a plan string from this bundled SQLite version will match another SQLite release, PostgreSQL, or MySQL.

How to use SQLite EXPLAIN QUERY PLAN

EXPLAIN and EXPLAIN QUERY PLAN are different

One small change, two different plans

Key points

Do not parse the text as a stable API

Practice this in Module 2: How queries run

Keep reading

References