Skip to main content

​
EXPLAIN

The EXPLAIN statement displays information about how Turso executes a SQL statement. There are two forms: EXPLAIN shows the virtual machine bytecode, and EXPLAIN QUERY PLAN shows the high-level query execution strategy.

​
Syntax

EXPLAIN statement;
EXPLAIN QUERY PLAN statement;
ParameterTypeDescription
statementSQLAny SQL statement (SELECT, INSERT, UPDATE, DELETE, etc.)

​
EXPLAIN

The EXPLAIN prefix causes Turso to return the sequence of virtual machine (VDBE) opcodes that would be used to execute the statement, rather than executing the statement itself. 
EXPLAIN SELECT * FROM users WHERE id = 1;
-- addr | opcode     | p1 | p2 | p3 | p4       | p5
-- 0    | Init       | 0  | 9  | 0  |          | 0
-- 1    | OpenRead   | 0  | 2  | 0  | 3        | 0
-- 2    | SeekRowid  | 0  | 8  | 1  |          | 0
-- ...

​
Output Columns

ColumnDescription
addrInstruction address (sequential integer)
opcodeThe operation name (e.g., OpenRead, SeekRowid, Column, ResultRow)
p1First operand
p2Second operand
p3Third operand
p4Fourth operand (often a string value like table name or collation)
p5Fifth operand (flags)

​
EXPLAIN QUERY PLAN

The EXPLAIN QUERY PLAN prefix provides a high-level description of the strategy the query optimizer chose for executing a statement. This output is more useful than raw EXPLAIN for understanding query performance. 
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice';
-- id | parent | notused | detail
-- 2  | 0      | 0       | SCAN users

CREATE INDEX idx_name ON users(name);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice';
-- id | parent | notused | detail
-- 3  | 0      | 0       | SEARCH users USING INDEX idx_name (name=?)

​
Output Columns

ColumnDescription
idA unique identifier for this step
parentThe id of the parent step (0 for top-level)
notusedReserved for future use (always 0)
detailHuman-readable description of the execution step

​
Common Detail Messages

Detail PatternMeaning
SCAN tableFull table scan (no index used)
SEARCH table USING INDEX idx (col=?)Index lookup on the specified column
SEARCH table USING INTEGER PRIMARY KEY (rowid=?)Direct rowid lookup
USE TEMP B-TREE FOR ORDER BYA temporary B-tree is used for sorting
USE TEMP B-TREE FOR DISTINCTA temporary B-tree is used for deduplication
COMPOUND SUBQUERYIndicates a UNION, INTERSECT, or EXCEPT
CORRELATED SCALAR SUBQUERYA correlated subquery that returns a single value

​
Examples

​
Comparing Query Plans

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

-- Without index: full table scan
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
-- SCAN orders

-- After creating an index
CREATE INDEX idx_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
-- SEARCH orders USING INDEX idx_customer (customer_id=?)

​
Join Order

EXPLAIN QUERY PLAN
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE c.name = 'Alice';
-- SCAN orders AS o
-- SEARCH customers AS c USING INTEGER PRIMARY KEY (rowid=?)

​
Subqueries

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE active = 1);
-- SCAN orders
-- LIST SUBQUERY
--   SCAN customers

​
See Also

  • ANALYZE for collecting statistics that improve query plans
  • CREATE INDEX for creating indexes to speed up queries