Use EXPLAIN QUERY PLAN for an indexed lookup

By Batu · English technical notes

Also published in our primary archive.

EXPLAIN QUERY PLAN asks SQLite for a high-level description of how it intends to implement a statement. For a lookup filtered by sku, creating an index on product(sku) gives the planner an index it can choose. The SQLite EXPLAIN QUERY PLAN documentation explains that detail rows beginning with SEARCH describe a subset lookup and normally identify the index used.

The fixture inserts three products, creates idx_product_sku, and runs the plan for the same parameterized lookup that will fetch the product. The assertion searches the plan-detail strings for the index name rather than printing or comparing an entire plan row. It then executes the lookup and verifies that SKU B-200 returns Brush; the only output is therefore stable: lookup: Brush.

Treat the plan assertion as a focused regression check for this fixture, not as a portable application contract. SQLite explicitly warns that EXPLAIN QUERY PLAN output is for interactive debugging and that its format can change between releases. A different SQLite version, schema, data distribution, query expression, or statistics may choose a different valid plan. The sqlite3 calls shown are long-standing APIs and need no newer Python minimum; the result depends on the runtime SQLite library bundled with or linked by Python. Use measurements on representative data before making performance conclusions.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this article; verify behavior in the Python and SQLite versions you deploy.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE product (sku TEXT, name TEXT)")
con.executemany(
    "INSERT INTO product VALUES (?, ?)",
    [("A-100", "Apron"), ("B-200", "Brush"), ("C-300", "Cable")],
)
con.execute("CREATE INDEX idx_product_sku ON product(sku)")

plan = con.execute(
    "EXPLAIN QUERY PLAN SELECT name FROM product WHERE sku = ?", ("B-200",)
).fetchall()
assert any("idx_product_sku" in detail for _, _, _, detail in plan)

name = con.execute(
    "SELECT name FROM product WHERE sku = ?", ("B-200",)
).fetchone()[0]
assert name == "Brush"

print(f"lookup: {name}")
con.close()

lookup: Brush