Match the index
to the query.
Verify the result.

The problem

An application needs a tenant's 50 most recent orders. The query filters on tenant_id and sorts by created_at, then id. The id tie-breaker gives rows with identical timestamps a deterministic order. This experiment asks whether one composite index can support both filtering and ordering without changing the returned rows.

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

The experiment, step by step

1. Construct a repeatable fixture.

   Create 50,000 synthetic orders in an in-memory SQLite database. Half belong to tenant 1; the rest are distributed across 199 tenant IDs. Repeated timestamps exercise the tie-breaker. Query a large tenant, a smaller tenant, and an absent tenant.

2. Record the baseline and check correctness.

   Run ANALYZE, inspect EXPLAIN QUERY PLAN, and compare the returned rows against an independently filtered and sorted JavaScript fixture. After 10 warmups, record 31 prepared-statement executions for each query.

3. Add the index, repeat, and compare.

   Create the index below, refresh statistics, and repeat the measurements. Compare exact ordered results before and after indexing. Record allocated database size and index creation time alongside query timing.

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

What the query plan showed

Before indexing, all three scenarios reported a table scan and a temporary B-tree for ordering:

SCAN orders
USE TEMP B-TREE FOR ORDER BY

After indexing, each reported an index search. This index starts with the equality filter and then follows the requested sort order:

SEARCH orders USING INDEX orders_tenant_recent (tenant_id=?)

The plan is evidence about how SQLite executed this query in this fixture. It is not a promise that another engine or dataset will make the same choice. The index is not covering for total_cents, so this example still needs table data.

Recorded measurements

One run used Node.js 24.19.0, SQLite 3.53.3, and Windows x64. Each value below is the median of 31 warm executions. Timing includes query execution and JavaScript row materialization; statement preparation, fixture loading, and index creation are excluded.

All six independent expected-result checks passed, as did all three comparisons of ordered results before and after indexing. The separate automated suite passed three tests covering fixture properties, correctness, and invalid experiment bounds.

The cost belongs in the result

Creating the index took 12.5692 ms in this run. Allocated database pages increased from 929,792 to 1,810,432 bytes, a change of 880,640 bytes after index creation and statistics updates. That measures the total allocated database change, not an exclusive index-file size. Write throughput was not measured; maintaining an additional index adds work that a production evaluation must examine.

Reproduce the study

Use Node.js 24 or later. The source has no external dependencies and creates no persistent database. Run the tests, then generate your own results:

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

Source, tests, and method on GitHub ↗ · Download the recorded measurements

What this does not establish

This is a small, warm, in-memory experiment in one environment. Baseline measurements precede indexed measurements, so execution-order effects are not controlled. It does not measure cold disk access, network latency, concurrent users, sustained writes, or production workloads. Timings will vary. No speed threshold is treated as a correctness test.

This case study was created in September 2026 with AI assistance, using synthetic data. It is not historical client work or a claim about results delivered for a customer.

The practical takeaway

Start from the filter and ordering requirements. Inspect the plan, verify exact results, and measure costs as well as elapsed time. A faster query is useful evidence only when the experiment explains what changed and where its conclusions stop.