A Reproducible Benchmark Protocol for Laravel Queries

Most "I optimised it and it's faster now" claims in Laravel projects rest on one refresh of a page in a local browser. That is not a measurement; it is an anecdote. Before you add an index, rewrite a query, or argue for a cache layer, you need a reproducible Laravel query benchmark: the same data, the same conditions, enough runs to see past noise, and a clear separation between time spent in MySQL and time spent in PHP.

This article is the protocol I use, along with the results of applying it to a one-million-row table while writing this series. One of those results was a trap I walked into myself, which is exactly why the protocol has the steps it has.

Step 1: Freeze the question before you measure

A benchmark answers one question. Write it down before running anything, in a form that can be falsified:

• Bad: "Is the orders page slow?"
• Good: "For a customer with ~20 orders, does an index on (customer_id, status, created_at) reduce the median time of the 'my paid orders' query compared to an index on (customer_id)?"

The good version names the query, the data shape, the change, the baseline, and the statistic. Everything that follows exists to keep those fixed while only the change varies.

Step 2: Build data that looks like production

Performance problems are a property of data volume and distribution, not of code alone. A query that scans 1,000 rows in development will look identical with or without an index. My test dataset for this series:

• MySQL 8.4.11 in Docker on an Apple M2 Pro, innodb_buffer_pool_size of 1 GB.
• An orders table with 1,000,000 rows generated from a fixed random seed, so anyone running the script gets the same data.
• About 50,000 customers with ~20 orders each, and a deliberately skewed status column: 70% paid, 15% pending, 10% cancelled, 5% refunded.

The skew matters. On this data, a single-column index on status made a filtered query 4.5× slower than having no index at all (629 ms vs 138 ms), because 70% of rows match and every match costs a random lookup into the clustered index. With a uniform distribution you would never see that. If you cannot copy production data, copy its shape: row counts, cardinality, and the most common values of the columns you filter on. A GROUP BY on production gives you that without exposing personal data.

SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT COUNT(DISTINCT customer_id), COUNT(*) FROM orders;
SELECT ROUND(AVG(c)), MAX(c) FROM (SELECT COUNT(*) c FROM orders GROUP BY customer_id) t;

Run ANALYZE TABLE after seeding so optimiser statistics match the data you just created.

Step 3: Isolate the variable

Between variants, change exactly one thing. When comparing indexes, my harness drops every secondary index before creating the one under test. Otherwise a leftover index from a previous variant quietly gets chosen and you measure the wrong thing.

function dropIndexes(PDO $pdo): void {
    $rows = $pdo->query("SHOW INDEX FROM orders WHERE Key_name <> 'PRIMARY'")
                ->fetchAll(PDO::FETCH_ASSOC);
    foreach (array_unique(array_column($rows, 'Key_name')) as $name) {
        $pdo->exec("DROP INDEX `$name` ON orders");
    }
}

Step 4: Warm up, repeat, report the median and p95

The first execution of a query pays for things the hundredth does not: reading pages from disk into the buffer pool, preparing the statement, filling CPU caches. Decide whether you are measuring a cold or warm system, and then be consistent. For query and index comparisons I measure warm: a few unmeasured runs, then 30 to 50 measured runs.

/** Returns [median_ms, p95_ms] after $warmup unmeasured executions. */
function bench(PDO $pdo, string $sql, array $params = [], int $runs = 30, int $warmup = 5): array
{
    $stmt = $pdo->prepare($sql);
    for ($i = 0; $i < $warmup; $i++) { $stmt->execute($params); $stmt->fetchAll(); }

    $times = [];
    for ($i = 0; $i < $runs; $i++) {
        $start = hrtime(true);
        $stmt->execute($params);
        $stmt->fetchAll();                       // include transfer of the result
        $times[] = (hrtime(true) - $start) / 1e6;
    }
    sort($times);

    return [$times[intdiv($runs, 2)], $times[(int) floor($runs * 0.95) - 1]];
}

Report the median rather than the mean: one garbage-collection pause or a noisy neighbour should not move your headline number. Report p95 alongside it, because users experience the tail. Use hrtime(), not microtime(); it is monotonic and has nanosecond resolution.

Always fetch the result inside the timed block. Timing only execute() can make an unbuffered query look nearly free while the expensive part, streaming rows to PHP, happens afterwards.

Step 5: Confirm the plan with EXPLAIN ANALYZE, not EXPLAIN

Plain EXPLAIN shows the optimiser's estimate. In this series, an index on (created_at, customer_id, status) produced an EXPLAIN row estimate of 20 for a customer lookup. It looked excellent. Measured, it took 965 ms, seven times slower than no index. EXPLAIN ANALYZE (MySQL 8.0.18+) executes the query and shows what actually happened:

-> Limit: 20 row(s)  (cost=2 rows=0.2) (actual time=220..1111 rows=15 loops=1)
    -> Filter: ((orders.status = 'paid') and (orders.customer_id = 37448))
        -> Index scan on orders using ix_ccs (reverse)
           (cost=2 rows=20) (actual time=0.204..1051 rows=1e+6 loops=1)

Estimated 20 rows; actually read one million. The optimiser assumed it would find 20 matches early while walking the index in date order. This customer had only 15 paid orders, so it walked the whole index. Whenever estimated and actual rows differ by orders of magnitude, your benchmark has found something the plan alone would have hidden. The full index comparison is in my Persian guide to choosing a composite MySQL index for Laravel queries.

Step 6: Separate SQL time from PHP time

A Laravel request does far more than run SQL. Eloquent hydrates models, applies casts, and serialises them to JSON. Measure both layers in the same run: DB::listen() reports the time MySQL took, and hrtime() around the whole call reports what your user waits for.

$sqlMs = 0;
DB::listen(function ($query) use (&$sqlMs) { $sqlMs += $query->time; });

$start = hrtime(true);
$payload = Order::query()
    ->where('status', 'paid')->orderByDesc('created_at')->limit(5000)
    ->get(['id', 'total', 'created_at'])
    ->toArray();
$totalMs = (hrtime(true) - $start) / 1e6;

printf("total=%.1fms sql=%.1fms outside-sql=%.1fms\n", $totalMs, $sqlMs, $totalMs - $sqlMs);

Results for 5,000 rows on the same dataset (PHP 8.2, Laravel 8.83, Xdebug off):

Variant	Total	SQL	Outside SQL
Query builder toBase()->get()	8.5 ms	8.4 ms	0.1 ms
Eloquent get(), 3 columns	20.1 ms	10.9 ms	9.2 ms
Eloquent get() + toArray() with casts	313.2 ms	18.1 ms	295.1 ms

The database was never the problem in the last row: 94% of the time went to PHP. To find out which part, I isolated the casts on the same 5,000 models (median of 11 runs each):

Model casts	get()->toArray()
None	38.4 ms
'total' => 'decimal:2'	70.9 ms
'created_at' => 'datetime'	160.6 ms
Both	183.7 ms

Creating a Carbon instance and formatting it for every row is the dominant cost. Two separate harness runs gave different absolute numbers for the "both casts" case (313 ms and 184 ms, the first model also had Eloquent's default timestamp handling), which is itself a reminder to compare variants within one run rather than across sessions. The conclusion held in both: for large exports or API responses, select fewer rows, paginate, or use the query builder and format dates once. No index would ever have fixed this endpoint.

Step 7: Control the environment, including your debugger

My first PHP-side measurements were wrong by an order of magnitude. Xdebug 3 was loaded with xdebug.mode=develop, a common local default. With it on, the toArray() case took 4,566 ms; with XDEBUG_MODE=off, 313 ms. A 14.6× distortion that only affected PHP time, so it made Eloquent look far worse relative to SQL than it really is.

Before trusting any number, record:

• php -v (is Xdebug or another profiler loaded?) and run with XDEBUG_MODE=off.
• Whether OPcache is enabled. The CLI usually has opcache.enable_cli=0, while PHP-FPM in production has it on. For framework-heavy code paths, benchmark through FPM or enable it in the CLI.
• APP_DEBUG and the query log. DB::enableQueryLog() keeps every query in memory and adds overhead.
• MySQL version, buffer pool size, and whether the data fits in memory. Production may be I/O-bound when your laptop is not.
• Hardware, and whether anything else heavy is running.

Step 8: Write it up so someone else can rerun it

A benchmark nobody can reproduce cannot settle an argument in a pull request. My template is short:

Question:     Does index X reduce median latency of query Q vs index Y?
Data:         orders, 1,000,000 rows, seed 20261012, status skew 70/15/10/5
Environment:  MySQL 8.4.11 (Docker), buffer pool 1 GB, PHP 8.2 CLI, Xdebug off
Method:       drop secondary indexes; create variant; 3 warm-up + 50 runs; median/p95
Result:       Y 0.37 ms / 0.47 ms → X 0.28 ms / 0.35 ms
Plan check:   EXPLAIN ANALYZE actual rows 15 (expected ≤ 20); no filesort
Decision:     Adopt X; drop index Y (covered by X's leftmost prefix)

Notice the decision in that example. The composite index won, but by less than a tenth of a millisecond, because customers only have ~20 orders. The real reason to adopt it is that it removes a filesort that would hurt customers with thousands of orders, and lets an old index be dropped. A benchmark should inform the decision, not replace judgement about the data you will have next year.

Common mistakes this protocol prevents

• Measuring once. A single run captures noise along with signal; the first run also pays cold-cache costs later runs do not.
• Testing on tiny data. Every index looks good with 1,000 rows.
• Trusting EXPLAIN estimates. Confirm actual rows with EXPLAIN ANALYZE.
• Timing only the database. Hydration, casts, and serialisation can dominate.
• Leaving a debugger on. Xdebug can inflate PHP time by more than 10×.
• Comparing across sessions. Compare variants inside the same run and environment.

Where to go next

For index design itself, the companion article on MySQL indexing for Laravel applications covers the rules this benchmark puts to the test. If you'd rather have someone measure your application and come back with a prioritised, evidence-based list of what to fix, that is what my Laravel performance optimisation and audit engagement is for.

Frequently asked questions

How many runs does a query benchmark need?

Enough for a stable median: a few unmeasured warm-up runs, then roughly 30 to 50 measured runs for fast queries and fewer for slow ones.

Should I benchmark with production data?

Copy its shape rather than its contents: row counts, cardinality, and value distribution matter more than the actual personal data.