Validate iOS SQLite query plans with composite indexes and scan budgets

A customer’s order list opens instantly in the test database, then slows noticeably after historical data is imported. For a development studio delivering multiple projects on a cloud Mac, the first acceptance check should be a query-plan review against fixed data—not repeated timing runs on different machines. The former helps uncover missing indexes; the latter can mistake cache effects and load fluctuations for optimization gains. This article uses a single order query to build a workflow from command-line preflight checks to verification through the actual iOS database connection.

1. Define the query contract before discussing indexes

The list must return only completed orders for one customer, ordered by creation time descending, with ties broken by ID descending, and capped at twenty rows per request. Tests must check filtering, ordering, and row count—not just elapsed time.

Prepare three sets of parameters: a customer with records, a nonexistent customer, and a customer with a high concentration of records. Small, uniformly distributed datasets are useful for checking logic, but they do not represent real business distributions. In production, a few large customers may account for most records.

Record sqlite_version(), the table schema, index definitions, and how statistics are generated. The command-line tool bundled with macOS may use a different engine from the app, especially when the project integrates SQLite itself. Do not treat their plans as a single shared result.

A query plan is the optimizer’s choice, not a stable text interface. Do not make exact, character-for-character equality of the entire output a cross-version acceptance gate.

2. Build a fixture that never touches customer databases

Run the following script in a terminal. It creates a separate temporary directory and does not connect to a production database. Record IDs are fixed, so repeated runs produce the same logical contents. Keeping the SQL file alongside the database makes the check easy to rerun at delivery.

set -eu
work="$(mktemp -d)"
cat > "$work/check.sql" <<'SQL'
.bail on
SELECT sqlite_version();
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
WITH RECURSIVE seq(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM seq WHERE x < 10000
)
INSERT INTO orders
SELECT x, x % 100,
       CASE WHEN x % 3 = 0 THEN 'done' ELSE 'open' END,
       x / 10
FROM seq;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX orders_customer_status_time
ON orders(customer_id, status, created_at DESC, id DESC);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
SQL
sqlite3 "$work/fixture.sqlite" < "$work/check.sql"
printf '%s\n' "$work"

Save the terminal output first, then run the actual query and check its results. The fixture deliberately creates records with identical creation times so that you can verify the second sort key takes effect. Checking only the newest record would miss changes in the ordering of records with matching timestamps.

3. Read access paths, not memorized plan strings

Without an index, you will typically see a scan of the orders table and a temporary structure for sorting. After creating the index, check whether the engine looks up rows by customer and status, and whether it still needs an additional sort. The wording varies across SQLite versions; do not assume the output will always be identical.

The composite index puts equality predicates first, followed by the sort columns, so the engine can read the first twenty rows along the target range. It also covers the columns selected by this example. If large fields are added to the query later, the index may no longer cover it. Run the acceptance checks again rather than continuing to cite the old report.

Do not treat SCAN as an automatic failure: an ordered scan of a covering index is not the same as filtering a large table row by row. Conversely, SEARCH does not guarantee a small amount of work. A low-selectivity condition can still require reading many records.

Indexes are useful for frequently accessed lists, not something to add indiscriminately to every field. They increase write and storage costs. When submitting the change, run insert, update, and delete tests as well, and confirm that the read optimization has not exceeded the delivery budget.

4. Check scan budgets through the actual connection

Once the command-line checks pass, import the fixture into the app’s test environment and execute the same parameterized query through the database wrapper the project actually uses. Keep bound parameter types consistent, and step the statement through to completion. Do not stop counting after fetching the first row.

If the underlying connection is accessible, use sqlite3_stmt_status to reset SQLITE_STMTSTATUS_FULLSCAN_STEP to zero before execution, then read it afterward. Also use SQLITE_STMTSTATUS_SORT to check the number of sort operations. The former counts steps associated with full-table scans; the latter is neither elapsed time nor the number of rows sorted.

Even when both counters are zero, the query is not free. It may still perform substantial index access. You can also record SQLITE_STMTSTATUS_VM_STEP and assess it alongside the number of rows returned. If the wrapper does not expose the statement handle, add a test adapter layer first. Do not parse elapsed times from application logs and present them as scan statistics.

Budgets should come from a reviewed baseline for the fixed fixture, not from generic thresholds. Reset counters before each measurement so that reusing a prepared statement does not carry over counts from the previous execution.

5. Include distribution changes in delivery checks

Keep at least two databases for acceptance testing: one with a uniform distribution to check the basic access path, and one with a skewed distribution to simulate large customers. In both, check the twenty-row limit, stable ordering, empty results, and different status parameters.

Statistics affect the plan. Record results separately for a newly created database and after running ANALYZE. If the product does not generate statistics at runtime, testing only the ideal case with statistics is not enough. Nor should you force an index just to produce an attractive plan and conceal the optimizer’s actual choice.

Finally, archive the SQL, engine version, description of the data distribution, index definitions, plans, and counters. When a new regression appears, compare this evidence before looking at elapsed time. Even after the studio switches cloud Macs or a project is delayed, the same fixture should still let you explain whether a change altered the access path—or whether one run simply happened to benefit from a warm cache.

Frequently asked questions