TopolearnA missing index makes SQLite scan whole tables; one well-chosen index can make a query thousands of times faster.
EXPLAIN QUERY PLAN shows how SQLite will run a statement, one line per step:
SCAN t— reads the whole table.SEARCH t USING INDEX ix (col=?)— uses an index to find rows.SEARCH t USING INTEGER PRIMARY KEY (rowid=?)— direct rowid lookup, the fastest.USING COVERING INDEX— the index has all needed columns, so the table isn't touched.USE TEMP B-TREE FOR ORDER BY— an extra sort step an index could avoid.
Indexes in SQLite follow the usual B-tree rules: a composite index serves queries on its leftmost columns, equality columns go first and the range or sort column last. Partial indexes (WHERE clause) and expression indexes are supported. Every index entry also stores the rowid.
SQLite's planner is cost-based but has little information by default. `ANALYZE` gathers statistics into sqlite_stat1 (and sqlite_stat4 if compiled in) to help it choose between indexes. The recommended routine is `PRAGMA optimize`, which runs ANALYZE only where it's likely to help: run it periodically, or just before closing long-lived connections.
Other performance basics: wrap bulk inserts in one transaction (each autocommitted statement is a separate fsync), reuse connections, and use executemany. With those, SQLite handles hundreds of thousands of inserts per second.
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer INTEGER, total REAL, day TEXT)")
with con: # one transaction for the bulk insert
con.executemany("INSERT INTO orders (customer, total, day) VALUES (?, ?, ?)",
((i % 1000, i * 0.5, f"2026-05-{i % 28 + 1:02d}") for i in range(100_000)))
q = "SELECT day, total FROM orders WHERE customer = 42 ORDER BY day"
plan = lambda: [row[3] for row in con.execute("EXPLAIN QUERY PLAN " + q)]
print(plan()) # ['SCAN orders', 'USE TEMP B-TREE FOR ORDER BY']
con.execute("CREATE INDEX orders_customer_day ON orders (customer, day, total)")
print(plan()) # ['SEARCH orders USING COVERING INDEX orders_customer_day (customer=?)']
con.execute("PRAGMA optimize")
print(con.execute("EXPLAIN QUERY PLAN SELECT * FROM orders WHERE id = 7").fetchall()[0][3])
# SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)Remember
- SCAN = full table read; SEARCH = index or rowid lookup
- Composite and covering indexes remove scans and temp B-tree sorts
- Batch writes in one transaction; run PRAGMA optimize periodically
Watch out for
- Inserting thousands of rows in autocommit mode, paying a disk sync per row.