Evaluate database indexes with reproducible measurements

A database index earns its place when it reliably improves an important workload and its additional costs remain acceptable. One unusually fast run cannot establish that. A useful decision needs an unchanged business result, an understandable access plan and measurements with an explicit scope. Otherwise a small difference in elapsed time can become an unsupported performance promise.

This article investigates one candidate using PHP and SQLite. Two synthetic data sets, two different read queries and a rolled-back insertion experiment show why an index does not automatically accelerate every list. The figures come from an actual experiment performed on 9 November 2026. They describe that environment and that data distribution. Capacity planning for a production website requires additional measurements of its own workload.

State the hypothesis before measuring

The first question is not which column deserves an index. It is which recurring task causes meaningful waiting under which conditions. A customer view might show the latest events for exactly one account. A different view might need the latest ordinary events across all accounts. Both read the same table, but their filters differ. They consequently place different demands on a useful ordering of stored keys.

Our hypothesis for the first view is specific: a composite index starting with account and event kind, followed by descending chronological order, will shorten the search for the latest 25 rare events of one account. The second view deliberately acts as a counterexample. It has no account restriction. Sharing a table name is insufficient reason to expect the same benefit. Whether the optimizer finds another useful path is observed rather than assumed.

A good hypothesis can be rejected. If the correct result remains unchanged but the plan saves no relevant work and elapsed time stays within ordinary variation, there is initially no convincing evidence of benefit. A different key order might deserve investigation. The conclusion might also be that this small table and infrequent query do not justify another index. A reasoned decision to leave the schema alone is a valid engineering outcome.

Define the data being stored

The experiment creates separate databases containing 10,000 and 50,000 events. Every record is fictional and unrelated to customer information. Account IDs are generated deterministically from the sequential event ID and distributed across 97 accounts. Every fifth event receives the kind rare; all others receive common. Several rows deliberately share a timestamp. A second comparison on the unique ID still establishes an unambiguous order.

Each row also contains a payload made from 200 identical ASCII characters. That is a controlled property of the fixture. It does not represent a typical product description, an actual log message or a realistic compression distribution. This disclosure matters because wide rows, different character sets and variable text lengths can have different storage and access characteristics. Two record counts do not replace an examination of representative data types.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    payload TEXT NOT NULL
);

CREATE INDEX events_account_kind_recent
ON events (account_id, kind, created_at DESC, id DESC);

The second statement is executed only after the first measurement phase. The primary key remains present throughout. In this article, “without the index” therefore means without the additional candidate index. It does not mean that the database has no ordered primary-key structure at all. Careless terminology can distort a comparison before anyone examines a single timing observation.

Keep the result contract unchanged

The targeted query filters for account 42 and the rare event kind. It returns only the ID and timestamp. The broad counterexample filters only for the common kind and also returns the payload. Both lists sort by descending timestamp followed by descending ID. The limit of 25 rows is part of the business task and stays identical before and after the schema change.

SELECT id, created_at
FROM events
WHERE account_id = :account_id AND kind = :kind
ORDER BY created_at DESC, id DESC
LIMIT 25;

SELECT id, created_at, payload
FROM events
WHERE kind = :kind
ORDER BY created_at DESC, id DESC
LIMIT 25;

A faster response would be worthless if an older event replaced a newer one. The test program therefore computes the expected list separately in PHP. It filters the original records, sorts them with an independent comparator and applies the limit afterwards. Every measured SQL execution is compared with that complete reference list outside the timed interval. Matching row counts alone would fail to detect substituted or incorrectly ordered records.

Shared timestamps exercise another easily missed edge case. Without the second ordering column, rows with equal time values could appear in different orders. An apparent change caused by an index might actually expose an order that had never been fully specified. The business ordering is therefore made explicit first. The index can improve its implementation afterwards without quietly redefining what the application promises to display.

Make the timing boundary explicit

The environment used PHP 8.4.27 with SQLite 3.53.4 on Windows. Each database was created in memory. There was no database-server connection or network path. The results consequently exclude server queues, connection establishment, network transfer and rendering a complete website. A cold filesystem cache was not investigated either. These exclusions are essential to understanding the very small recorded values.

Before each read phase, the program performed three warm-up executions per query. Eleven measured rounds followed. Within each round, the targeted query ran first and the broad query second. The timed interval contained execute() and fetchAll(). Statement preparation, comparison with the reference result and construction of the original data were outside that interval. Its scope is therefore narrower than the duration of a complete HTTP request.

Timing uses PHP's monotonic high-resolution hrtime clock. A monotonic clock is suitable for elapsed intervals because changing the calendar clock does not adjust the measured interval in the same way. Nanoseconds are converted to milliseconds for reporting. A fine numerical unit does not by itself make one tiny observation meaningful. Operating-system scheduling and other processes can still influence the measurement.

$start = clockNs();
$statements[$name]->execute($query['params']);
$actual = $statements[$name]->fetchAll();
$elapsed = (clockNs() - $start) / 1000000;
check($actual === $references[$name], 'Timed query result mismatch');
$phaseData['queries'][$name]['samples_ms'][] = $elapsed;

This excerpt comes from the executed measurement loop. Its clockNs() helper verifies that hrtime(true) returned an integer. The complete program rejects an unsuitable timer result by throwing an exception. A failed clock call is consequently not treated as a valid zero. Result verification happens after the second clock call, so that comparison is not directly included in the recorded query interval.

Read the actual timing observations

The following table reports the median of eleven recorded executions for each cell. Every timing value is in milliseconds. The figures were copied from the saved experiment report. Four decimal places support traceability within this small experiment. They do not promise equivalent repeatability on a subsequent run and do not specify a guaranteed response time for a customer application.

For the targeted list, observed medians with the candidate are substantially below the observations without it. That supports the stated hypothesis. The broad list shows a mixed picture: its value is higher afterwards in the smaller fixture and somewhat lower afterwards in the larger fixture. These observations do not establish a general acceleration of that second query. Its unchanged execution plan provides another reason to avoid such a claim.

The median is the middle value after sorting the eleven observations. Isolated outliers influence it less directly than a simple arithmetic mean. It is nevertheless not a confidence interval, a high-load latency percentile or proof of statistical significance. The private experiment report retains every observation together with minimum and maximum values. A production decision should inspect that variation instead of selecting the most attractive number for a presentation.

Use the plan to explain the difference

Without the candidate, SQLite reported a table scan and an additional temporary sorting structure for both queries. Afterwards, the targeted query used a covering search through events_account_kind_recent. The broad query retained the table scan and temporary sorting structure. These are observed plans from this particular SQLite version with these statistics. Different distributions can lead the optimizer to make different choices.

Targeted list before:
SCAN events
USE TEMP B-TREE FOR ORDER BY

Targeted list after:
SEARCH events USING COVERING INDEX events_account_kind_recent
    (account_id=? AND kind=?)

Broad list before and after:
SCAN events
USE TEMP B-TREE FOR ORDER BY

The index starts with account and kind. Both values are fixed for the targeted list. Timestamp and ID then follow in the required order. The two selected columns are present in the index. The SQLite query-planning documentation explains composite and covering indexes. Here, that structure explains the observed improvement without establishing a universal rule about the best column order for every application.

The broad list lacks the first filtering condition. It also requests the payload, which this index does not contain. However, saying that an optimizer can never choose any useful indexed access under such conditions would be too broad. The actual plan decides the diagnosis. The EXPLAIN QUERY PLAN documentation also warns that its presentation format is not a stable application interface. A diagnostic report can display those lines, while business logic should not depend on matching their exact wording.

Include write work and storage costs

Additional indexes must be maintained when relevant data changes. The supplementary experiment therefore inserted 200 new events inside a transaction on every iteration and then rolled the whole transaction back. Two warm-up iterations preceded eleven recorded iterations. The timed interval included transaction start, prepared insertions and rollback together. After every iteration, a separate check verified that the original record count had been restored.

The observed insertion-batch medians increased after the index was created. That makes additional maintenance work visible in this experiment. It does not measure durable writes to an SSD. The database lived in memory and every batch ended with rollback. A realistic write workload with commits, journaling, backup policy and concurrent readers needs its own experiment. In particular, the difference should not be marketed as the universal cost of every individual insertion.

The program also recorded database pages before running the insertion experiments. At a page size of 4096 bytes, the smaller fixture grew from 561 to 620 pages and the larger fixture from 2788 to 3088 pages. This describes the page extent of the entire database in each phase. It is neither a measurement of PHP process memory nor an exact isolated measurement of the candidate index alone.

The official PRAGMA reference defines page_count and page_size. An operational decision would additionally consider backup volume, available headroom and subsequent maintenance time. A faster read path can be worthwhile even when it consumes space. That trade-off becomes useful only when the observed benefit and the costs actually investigated are identified separately.

Document statistics and experiment order fairly

The test program runs ANALYZE before each phase. That work is excluded from measured query time. The optimizer therefore receives statistics describing the corresponding state in both phases. This is a deliberate condition of the controlled experiment. For ongoing operation, SQLite now recommends PRAGMA optimize in many situations. Its ANALYZE documentation describes that maintenance approach. This fixture is not an instruction to analyze every table before each web request.

Phases always ran in the same order: first without the additional index, then with it. This limits the conclusion because background load and thermal conditions can change during a run. Three warm-up rounds do not remove that possibility. A stronger comparison would use several fresh databases, alternate phase order and repeat runs at different times. A published method should preserve those limitations instead of making the experiment appear more controlled than it was.

Parameter distribution matters as well. One account returning its 25 latest matches does not represent an exceptionally large account or an almost empty account. A real application needs a small, justified family of parameters: frequent and rare values, small and large tenants and both matching and empty results. Its business sorting and authorization requirements must stay unchanged. Otherwise the comparison quietly switches between different tasks.

It is also useful to record which quantities are intentionally absent. This run has no concurrent request generator, no durable commit latency and no network retry policy. Its two fixture sizes do not establish an asymptotic complexity law. They show how this candidate behaved at two controlled points. Those observations are enough to motivate the next test while remaining insufficient for a broad infrastructure recommendation.

Evaluate benefit within the complete application

An impressive individual measurement does not establish where development effort is best spent. The targeted list might be opened a thousand times a day or once per quarter. The broad list might instead be the most frequent entry point. Prioritization therefore needs request frequency, affected users and the waiting time they actually experience. Those quantities were not collected in this synthetic experiment. They must come from the application rather than being invented afterwards.

The timing boundary also helps with that decision. Improving a query does not reduce a website's duration by an equivalent proportion when an external delivery service accounts for most of its delay. Conversely, a short query repeated extremely often can matter under concurrency. The investigation should therefore consider both the individual list and its role in the complete process. An index is one possible intervention within that process.

A trustworthy comparison also needs stable surrounding conditions. If payloads are shortened, output columns removed and an index added simultaneously, the contribution of each change remains unclear. Such a combined improvement can be operationally useful. Its outcome should then be recorded as a combined change. Investigating the effect of a specific index requires changing that candidate while retaining the result contract.

The existing index collection needs maintenance too. Similarly named structures are not automatically equivalent. Uniqueness requirements, ordering and other queries can distinguish them. An apparently duplicated structure should therefore not be removed solely because its leading columns overlap. First examine its business purpose and actual consumers. Subsequent removal deserves the same attention to recovery, reversal and result verification as adding a new structure.

Transfer the method to other database systems

All timings in this article come from SQLite. No PostgreSQL or MySQL server was started for this experiment. Their diagnostic tools still matter when transferring the method. The PostgreSQL EXPLAIN documentation distinguishes estimated planning costs from measured elapsed times. Cost estimates are not directly readable milliseconds. Repeated inner plan nodes also need to be interpreted with their loop counts in mind.

Both PostgreSQL's ANALYZE option and MySQL 8.4 EXPLAIN ANALYZE actually execute the statement being examined. That is useful for read-only test cases and critical for statements that change data. Diagnosis must not silently create production changes. An appropriate isolated environment and a previously understood transaction scope are therefore part of the method. Rollback is not a blanket promise to reverse every possible side effect of an arbitrary statement.

The meaning of covering access, storage pages and physical row placement also varies between systems. Replacing a product name does not turn a SQLite plan into a PostgreSQL plan. What transfers is the set of questions: Is the result correct? Which work is avoided? What statistics support the choice? Which new costs appear? Answers must be collected again using the database actually deployed.

A decision matrix for the next investigation

Before a production change, this becomes a concrete decision record. It names the user task, the candidate, the result checks and the expected improvement within a defined workload range. It also explains when the change should be reversed or investigated again. Future data growth, a different ordering requirement or a new export column can alter the value of an index that was previously appropriate.

The deployment plan deserves its own treatment. Building an index on a small disposable in-memory table does not establish the locking behavior or build duration on a busy production database. Available disk space, recovery arrangements and engine-specific creation options must be checked separately. The experiment provides evidence for choosing a candidate; it does not itself authorize an unexamined schema operation during peak business activity.

A checklist for reproducible index experiments

• Describe the business list, filters, sorting and limit unambiguously.
• Distinguish existing primary keys from additional candidate indexes.
• Verify expected rows and order independently of the timed SQL.
• Document record counts, value distributions and synthetic properties.
• Record runtime versions and relevant database settings.
• Disclose warm-up, repetitions and the work included in timing.
• Retain raw observations and inspect variation alongside the median.
• Capture before-and-after plans with the same parameters.
• Investigate writes, storage and maintenance against actual usage.
• Do not describe an in-memory test as a disk or network benchmark.
• Set repeat-investigation and rollback criteria before adoption.

The executed experiment passed 168 checks in total. These include complete result comparisons for warm-up and timed queries, restored row counts after every insertion batch and database integrity checks. The number describes the verification scope of this example. It does not turn two synthetic fixtures into a general workload simulation. Keeping those statements separate permits a useful engineering conclusion without exaggerating what the evidence proves.