SQLite EXPLAIN QUERY PLAN

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.

EXPLAIN and EXPLAIN QUERY PLAN are different

EXPLAIN QUERY PLAN gives a short tree that is useful for checking access methods. EXPLAIN gives the lower-level virtual machine instructions. Start with EXPLAIN QUERY PLAN when you want to know whether SQLite scanned a table, searched an index, or created temporary work.

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

One small change, two different plans

Schema

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

Sample data

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

This filter wraps the indexed column in a function:

Before query

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

SQLite can report:

Before plan

SCAN customers

If the stored email values already use the required case, a direct match can use the index:

Change

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

After query

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

After plan

SEARCH customers USING INDEX idx_customers_email (email=?)

Both forms can return the same row in this sample. The plans differ because the plain index stores email, not lower(email). Real applications must preserve the intended case rules before making this change.

Read the detail field

Find each SCAN or SEARCH record and name the table it reads.
For a search, note the index name and the terms shown 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 changing 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 tests that check the behavior you own. Do not promise that a plan string from this bundled SQLite version will match another SQLite release, PostgreSQL, or MySQL.