Optimizing SQLite Queries: Indexes, EXPLAIN QUERY PLAN, and Common Mistakes

When a SQLite application becomes slow, adding indexes by instinct often increases storage and write cost without fixing the actual query. A better process measures a real workload, inspects its execution plan, designs an index around filtering and ordering, and measures again with production-like data.

Start with a slow query, not an index

Capture the SQL, parameters, duration, row count, and call frequency. A query taking 80 ms once an hour matters less than an 8 ms query executed thousands of times per action. Include lock time and ORM-generated query counts.

EXPLAIN QUERY PLAN
SELECT id, total
FROM orders
WHERE customer_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN QUERY PLAN shows whether SQLite performs a table SCAN, an indexed SEARCH, or creates a temporary B-tree for sorting. Its output is intended for interactive debugging and can change between SQLite versions, so application logic should not parse its description text.

Design composite indexes around access patterns

CREATE INDEX idx_orders_customer_status_created
ON orders(customer_id, status, created_at DESC);

Equality columns commonly come first, followed by range or ordering columns. Column order matters because a multi-column index follows a left-most-prefix rule. An index beginning with customer_id can help customer queries but usually offers little to a query filtering only by status.

Covering indexes have a cost

If an index includes every column needed by the query, SQLite may return results without looking up the table. Adding total to the example index could make it covering. The tradeoff is a larger database, more work on every write, and fewer useful pages in cache.

Do not create one index per query. Look for indexes that support several important access patterns and remove redundant prefixes only after verifying they are unused.

Why an index might not be used

• The table is small enough that a scan is cheaper.
• The indexed value has low selectivity.
• A function or transformation does not match an expression index.
• Parameter and column data types do not align.
• The query does not match the left-most prefix or returns most rows.
• Planner statistics no longer represent current data.

Avoid using INDEXED BY as a routine tuning hint. SQLite documents it primarily as a way to detect unwanted plan changes, not as a soft instruction to the planner.

Refresh statistics with PRAGMA optimize

SQLite recommends running PRAGMA optimize; periodically and after schema changes, especially after creating indexes. Long-lived connections can run PRAGMA optimize=0x10002; when opened and invoke the regular command periodically or before closing. It is usually a no-op and performs ANALYZE work only when useful.

Watch for N+1 and deep pagination

A fast query still creates a slow system when called once per result row. Use eager loading, joins, or batches to remove N+1 patterns. For large tables, cursor pagination such as WHERE id < ? ORDER BY id DESC LIMIT ? is often more stable than a deep OFFSET.

Safe optimization checklist

1. Reproduce with production-like data volume.
2. Benchmark before and after under equivalent cache conditions.
3. Read the plan instead of guessing.
4. Change one thing at a time.
5. Measure INSERT, UPDATE, storage, and migration impact.
6. Test correctness as well as speed.
7. Monitor slow queries after release.

Conclusion

SQLite optimization balances read speed, write overhead, and operational complexity. The query plan reveals what the database is doing; composite and covering indexes should be added only when measurements show that they improve an important access path.

References

• SQLite: EXPLAIN QUERY PLAN
• SQLite: Query Planning
• SQLite: ANALYZE and PRAGMA optimize