Use a covering index for a narrow lookup

By Batu · English technical notes

Also published in our primary archive.

Use a covering index for a narrow lookup

To use a covering index for SELECT label FROM items WHERE code=?, put the lookup column first and the returned column after it: CREATE INDEX ... ON items(code, label). The experiment first forces the single-column index so its plan is demonstrably non-covering, even though it returns the right label. It then replaces that index with (code, label) and checks the plan text for USING COVERING INDEX.

The schema and two synthetic rows are printed before either query. INDEXED BY is intentional here: tiny in-memory tables may otherwise make SQLite choose a table scan, which would obscure the comparison. It is a diagnostic device, not a general instruction to hard-code planner choices in application SQL. Both queries return alpha; the difference is whether the index contains every column needed by this particular query. The assertion proves the reported plan for this fixture, not a performance measurement for another data distribution or SQLite build.

SQLite describes a covering index as one containing both the search and output columns, allowing the table to be avoided for that query (SQLite query planner). Python’s sqlite3 module executes parameterized statements with bound values (Python sqlite3 documentation). No newer Python-only API is used; the example requires Python 3 with sqlite3 and a SQLite build that reports EXPLAIN QUERY PLAN details.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this reproducible synthetic example; the assertions define its claimed result.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE items(code TEXT, label TEXT, note TEXT)")
con.executemany(
    "INSERT INTO items VALUES (?, ?, ?)",
    [("A1", "alpha", "first"), ("B2", "beta", "second")],
)
print(con.execute("SELECT sql FROM sqlite_master WHERE name='items'").fetchone()[0])
print("seed=A1:alpha,B2:beta")

con.execute("CREATE INDEX idx_code ON items(code)")
naive_plan = con.execute(
    "EXPLAIN QUERY PLAN SELECT label FROM items INDEXED BY idx_code WHERE code=?",
    ("A1",),
).fetchone()[3]
naive_label = con.execute(
    "SELECT label FROM items INDEXED BY idx_code WHERE code=?", ("A1",)
).fetchone()[0]
assert "COVERING" not in naive_plan
print(f"naive result={naive_label}; covering={ 'COVERING' in naive_plan }")

con.execute("DROP INDEX idx_code")
con.execute("CREATE INDEX idx_code_label ON items(code, label)")
plan = con.execute(
    "EXPLAIN QUERY PLAN SELECT label FROM items INDEXED BY idx_code_label WHERE code=?",
    ("A1",),
).fetchone()[3]
label = con.execute(
    "SELECT label FROM items INDEXED BY idx_code_label WHERE code=?", ("A1",)
).fetchone()[0]
assert label == "alpha"
assert "USING COVERING INDEX idx_code_label" in plan
print(f"corrected result={label}; plan=USING COVERING INDEX")

CREATE TABLE items(code TEXT, label TEXT, note TEXT)
seed=A1:alpha,B2:beta
naive result=alpha; covering=False
corrected result=alpha; plan=USING COVERING INDEX