SQLite Query Plan Lab

A reproducible database demonstration by Gbolahan Odulate, created in September 2026 with AI assistance and entirely synthetic data.

Read the case study · Website · LinkedIn

Question

For a tenant's 50 most recent orders, can an index support both filtering and deterministic ordering while preserving the exact returned rows?

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = ?
ORDER BY created_at DESC, id DESC
LIMIT ?;

CREATE INDEX orders_tenant_recent
ON orders(tenant_id, created_at DESC, id DESC);

Reproduce

Use Node.js 24 or later. No dependencies or external database are needed.

node --test study.test.mjs
node study.mjs

The second command replaces results.json with your local measurements. The committed file records one Windows x64 run on Node 24.19.0 / SQLite 3.53.3. Timings vary across environments.

Method

The script loads 50,000 deterministic synthetic rows into an in-memory SQLite database. Half belong to tenant 1; remaining rows are distributed across 199 tenant IDs. Timestamps deliberately repeat; id breaks ordering ties.

It runs ANALYZE, measures the unindexed queries, creates the composite index, runs ANALYZE again, and measures indexed queries. For each phase and tenant it uses 10 warmups and 31 timed executions of a prepared statement. Timing includes query execution and JavaScript row materialization, excluding preparation, fixture loading and index creation.

The three tenants have 25,000, 126 and zero matching rows. Six checks compare returned rows to independently filtered and sorted JavaScript fixtures; three compare exact ordered results before and after indexing. Automated tests also cover fixture properties and invalid experiment bounds.

Recorded observations

Matching rows	Returned rows	Before median (ms)	After median (ms)
25,000	50	5.5444	0.0312
126	50	1.3425	0.0321
0	0	1.1695	0.0065

All three plans changed from SCAN orders plus USE TEMP B-TREE FOR ORDER BY to SEARCH orders USING INDEX orders_tenant_recent (tenant_id=?).

Index creation took 12.5692 ms. Total allocated database pages grew from 929,792 to 1,810,432 bytes, an increase of 880,640 bytes after index creation and statistics updates. This is not an exclusive index-file size measurement.

Limits

This is a small, warm, in-memory, single-process experiment in one environment. Baseline runs precede indexed runs; order effects are not controlled. It does not measure cold storage, network latency, concurrent users, sustained writes, other engines, or production workloads. The index is not covering for total_cents. No timing threshold is used as a correctness assertion.

This is newly created demonstration work, not a historical client project or a claimed client outcome. No private or real customer records are used.

References

• SQLite EXPLAIN QUERY PLAN
• SQLite query planner
• Node.js SQLite API