Change one factor at a time
The experiment compares the same table and query before and after adding an index. It selects owner equal to 42. Item identifiers range from zero through 9999, and owner is the remainder modulo one hundred, so one hundred rows match. Fixing the data and condition separates the meaning of the result from the method used to retrieve it.
The script uses the Python standard-library sqlite 3 interface and an in-memory database. It does not connect to a production service or open an existing data file. Some Python distributions omit this optional module, so verify that sqlite 3 imports in your environment before running the example.
Inspect results as well as the plan
The code runs EXPLAIN QUERY PLAN and the corresponding SELECT on each side of CREATE INDEX. It compares sorted results because no ORDER BY promises a retrieval order. A different order after index creation is not, by itself, a change in the matching rows.
SQLite versions can present plan details differently, so exact output-string equality is not the success criterion. Look for a change from a table scan to an access path using the owner index. Record the printed SQLite version with the observed plan rather than assuming every environment prints identical text.
import sqlite3
con = sqlite3.connect(":memory:")
try:
con.execute("CREATE TABLE items(item_id INTEGER PRIMARY KEY, owner INTEGER)")
con.executemany("INSERT INTO items VALUES (?, ?)",
((i, i % 100) for i in range(10000)))
sql = "SELECT item_id FROM items WHERE owner = ?"
before = con.execute("EXPLAIN QUERY PLAN " + sql, (42,)).fetchall()
rows_before = con.execute(sql, (42,)).fetchall()
con.execute("CREATE INDEX items_owner_idx ON items(owner)")
after = con.execute("EXPLAIN QUERY PLAN " + sql, (42,)).fetchall()
rows_after = con.execute(sql, (42,)).fetchall()
assert sorted(rows_before) == sorted(rows_after)
assert len(rows_after) == 100
print("SQLite:", sqlite3.sqlite_version)
print("Before:", before)
print("After:", after)
print("Matching rows:", len(rows_after))
finally:
con.close()
State what the observation establishes
Filtering a small subset by owner makes a matching index a way to narrow candidate rows. Assertions establish that both queries returned the same records and the expected count. The main observation is that an access strategy can change without changing the question answered by the query.
The script does not measure elapsed time. An index-related plan therefore cannot establish a numerical speedup. A small in-memory example also does not reproduce storage access in a large production workload. Keep the conclusion within the evidence actually collected instead of attaching an invented performance ratio.
Extend the experiment carefully
Reducing the number of possible owners increases the fraction matching each owner. Change that distribution separately and inspect the resulting plan. Returning additional columns can also change whether an index alone supplies the requested data. Retain the result-equivalence check when making these variations.
Indexes also consume space and add maintenance work during updates. This read-only comparison does not quantify write cost or concurrency effects. If timing is added later, record caching, repetition, distribution, and environment conditions. Interpret plan observations and timing measurements as related but distinct kinds of evidence.