EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN shows how SQLite intends to execute a query.

It is your first diagnostic tool for understanding slow reads.

Core Concepts

flowchart TD
  A[Write SELECT query] --> B[Prefix with EXPLAIN QUERY PLAN]
  B --> C[Read plan detail text]
  C --> D[Identify SCAN vs SEARCH]
  D --> E[Adjust index/query]

Plan clue	Meaning
SCAN table	Full/large table scan likely
SEARCH table USING INDEX ...	Index lookup path used
USING COVERING INDEX	Query can be satisfied from index alone

Scan Example

EXPLAIN QUERY PLAN
SELECT order_id, total
FROM eqp_orders
WHERE customer_id = 42;

detail
Often SCAN eqp_orders before adding index.

Index Search Example

CREATE INDEX idx_eqp_orders_customer
ON eqp_orders(customer_id);

EXPLAIN QUERY PLAN
SELECT order_id, total
FROM eqp_orders
WHERE customer_id = 42;

detail
Often SEARCH eqp_orders USING INDEX idx_eqp_orders_customer ....

Code Examples

-- Setup table for plan inspection.
CREATE TABLE eqp_orders (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO eqp_orders (customer_id, total)
VALUES (42, 99.0), (42, 40.0), (7, 10.0), (9, 12.0);

Expected output
Table created and rows inserted for plan testing.

-- Inspect execution strategy.
EXPLAIN QUERY PLAN
SELECT order_id, total
FROM eqp_orders
WHERE customer_id = 42;

id	parent	notused	detail
Plan row(s)	Plan row(s)	0	SCAN or SEARCH detail text

-- Execute actual query for result correctness.
SELECT order_id, total
FROM eqp_orders
WHERE customer_id = 42
ORDER BY order_id;

order_id	total
1	99.0
2	40.0

SQLite-Specific Nuances

SQLite Nuance

Exact detail wording can vary by SQLite version.

Focus on intent signals (SCAN, SEARCH, index name), not exact text formatting.

Common Pitfalls / Best Practices

Pitfall

Reading only query results and never checking planner output for hot paths.

Best Practice

For every important query, keep one saved EXPLAIN QUERY PLAN snapshot before and after tuning changes.

Quick Challenge

Run EXPLAIN QUERY PLAN for SELECT * FROM eqp_orders WHERE total > 50; and identify whether it scans or searches.

View Solution

EXPLAIN QUERY PLAN
SELECT *
FROM eqp_orders
WHERE total > 50;

Expected output
Likely SCAN eqp_orders unless an appropriate index on total exists.