TopolearnUnderstanding statement compilation explains the value of parameters, statement caching and how SQLite executes queries.
SQLite's architecture is a compiler plus a virtual machine. When you prepare a statement (sqlite3_prepare_v2 in C; the Python module does this for you), the tokenizer, parser and code generator (including the query planner) turn the SQL into a program for the VDBE (virtual database engine). Running the statement means stepping through that program: each sqlite3_step() executes opcodes until a result row is ready or the program halts.
EXPLAIN SELECT ... (without QUERY PLAN) lists the bytecode. Typical opcodes:
Init,Transaction,Goto— set-up and starting a read or write transaction.OpenRead/OpenWrite— open a cursor on a B-tree by its root page.Rewind/Next— loop over a table;SeekGE,IdxGT,SeekRowid— positioned index lookups.Column,Rowid— read values from the current row;ResultRow— emit a row.Halt— end of program.
Compilation is relatively expensive compared with executing a simple statement, so reusing prepared statements matters. Python's sqlite3 keeps a per-connection statement cache (cached_statements, 128 by default), keyed by the SQL text. That is another reason to use parameters: WHERE id = ? is compiled once and reused, whereas formatting values into the SQL creates a new statement every time.
Opcodes and their output can change between SQLite versions; treat EXPLAIN output as a debugging aid, not a stable interface.
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("CREATE INDEX t_name ON t (name)")
for addr, opcode, p1, p2, p3, p4, p5, comment in con.execute(
"EXPLAIN SELECT id FROM t WHERE name = ?", ("x",)):
print(addr, opcode, p1, p2, p3)
# 0 Init 0 ...
# ... OpenRead ... cursor on the t_name index B-tree
# ... SeekGE ... jump to the first entry >= ?
# ... IdxGT ... stop when past the value
# ... IdxRowid ... read the rowid stored in the index entry
# ... ResultRow ... emit one row
# ... Halt ...
# (exact addresses and operands vary by SQLite version)
for i in range(1000): # compiled once, then served from the statement cache
con.execute("SELECT name FROM t WHERE id = ?", (i,))Remember
- SQL is compiled to VDBE bytecode, then executed step by step
- EXPLAIN shows the bytecode; EXPLAIN QUERY PLAN the high-level plan
- Parameters let compiled statements be cached and reused
Watch out for
- Formatting values into SQL strings, which defeats the statement cache as well as being unsafe.