Making It Fast
Most slow PHP is not slow because of PHP. It is slow because of a query inside a loop, work repeated that could have been done once, or an array built by copying. This lesson is about finding which, and the three fixes that cover almost all of it.
Measure first
<?php
declare(strict_types=1);
function timed(string $label, callable $work): float
{
$start = hrtime(true);
$work();
$ms = (hrtime(true) - $start) / 1e6;
printf("%-22s %7.2fms%s", $label, $ms, PHP_EOL);
return $ms;
}
$rows = range(1, 50000);
$concat = timed('string concatenation', function () use ($rows): void {
$out = '';
foreach ($rows as $n) {
$out .= (string) $n . ',';
}
});
$implode = timed('implode', function () use ($rows): void {
$out = implode(',', $rows);
});
var_dump($implode < $concat);
Open this example in the editor and change it. Nothing you type is saved.
hrtime(true) gives nanoseconds from a monotonic clock, which is what you want - microtime() can move backwards when the system clock is adjusted.
For anything more than one measurement, use a profiler rather than scattering timers. Xdebug's profiler or an APM agent will tell you where the time went without you guessing where to look; a timer tells you only about the thing you already suspected.
And remember the ordering: measure, change one thing, measure again. A change that was not measured is a belief.
The N+1 problem
This is the single most common performance bug in applications that talk to a database, and it dwarfs everything else in this lesson.
<?php
declare(strict_types=1);
$pdo = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT)');
$pdo->exec('CREATE TABLE lessons (id INTEGER PRIMARY KEY, course_id INTEGER, title TEXT)');
for ($c = 1; $c <= 20; $c++) {
$pdo->prepare('INSERT INTO courses (id, title) VALUES (?, ?)')
->execute([$c, 'Course ' . $c]);
for ($l = 1; $l <= 10; $l++) {
$pdo->prepare('INSERT INTO lessons (course_id, title) VALUES (?, ?)')
->execute([$c, 'Lesson ' . $c . '.' . $l]);
}
}
$courses = $pdo->query('SELECT id, title FROM courses')->fetchAll();
// N+1: one query for the courses, then one PER COURSE.
$queries = 1;
$start = hrtime(true);
foreach ($courses as &$course) {
$statement = $pdo->prepare('SELECT COUNT(*) AS n FROM lessons WHERE course_id = ?');
$statement->execute([$course['id']]);
$course['lessons'] = (int) $statement->fetch()['n'];
$queries++;
}
unset($course);
$naiveMs = (hrtime(true) - $start) / 1e6;
printf("N+1: %d queries, %.2fms%s", $queries, $naiveMs, PHP_EOL);
// Two queries, whatever the number of courses.
$start = hrtime(true);
$counts = [];
foreach ($pdo->query('SELECT course_id, COUNT(*) AS n FROM lessons GROUP BY course_id') as $row) {
$counts[(int) $row['course_id']] = (int) $row['n'];
}
$courses = $pdo->query('SELECT id, title FROM courses')->fetchAll();
foreach ($courses as &$course) {
$course['lessons'] = $counts[$course['id']] ?? 0;
}
unset($course);
$batchedMs = (hrtime(true) - $start) / 1e6;
printf("batch: 2 queries, %.2fms%s", $batchedMs, PHP_EOL);
var_dump($batchedMs < $naiveMs);
printf("total lessons: %d%s", array_sum(array_column($courses, 'lessons')), PHP_EOL);
Open this example in the editor and change it. Nothing you type is saved.
Against an in-memory SQLite database the difference is milliseconds. Against a database over a network, each query costs a round trip - a millisecond at best - so twenty courses is twenty milliseconds and two thousand is two seconds. The shape of the fix is always the same: collect the ids, fetch in one query, index by id in PHP.
<?php
declare(strict_types=1);
// The general shape, for a one-to-many relationship.
$ids = [1, 2, 3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = 'SELECT course_id, id, title FROM lessons WHERE course_id IN (' . $placeholders . ')';
echo $sql, PHP_EOL;
// Then group in PHP, which is cheap:
$rows = [
['course_id' => 1, 'id' => 10, 'title' => 'a'],
['course_id' => 1, 'id' => 11, 'title' => 'b'],
['course_id' => 2, 'id' => 12, 'title' => 'c'],
];
$grouped = [];
foreach ($rows as $row) {
$grouped[$row['course_id']][] = $row;
}
print_r(array_map('count', $grouped));
Open this example in the editor and change it. Nothing you type is saved.
Work that belongs outside the loop
<?php
declare(strict_types=1);
$rows = range(1, 20000);
$start = hrtime(true);
$out = [];
foreach ($rows as $row) {
$formatter = new NumberFormatter('en_GB', NumberFormatter::DECIMAL); // built 20000 times
$out[] = $formatter->format($row);
}
$inside = (hrtime(true) - $start) / 1e6;
$start = hrtime(true);
$formatter = new NumberFormatter('en_GB', NumberFormatter::DECIMAL); // once
$out = [];
foreach ($rows as $row) {
$out[] = $formatter->format($row);
}
$outside = (hrtime(true) - $start) / 1e6;
printf("built inside: %7.2fms%s", $inside, PHP_EOL);
printf("built outside: %7.2fms%s", $outside, PHP_EOL);
var_dump($outside < $inside);
Open this example in the editor and change it. Nothing you type is saved.
The same applies to a prepared statement, a compiled pattern, a configuration lookup, and count() on an array that is not changing. The rule: anything whose result does not depend on the loop variable belongs before the loop.
count() inside a for header is the classic:
<?php
declare(strict_types=1);
$values = range(1, 100000);
$start = hrtime(true);
for ($i = 0; $i < count($values); $i++) { } // count() every iteration
$recounted = (hrtime(true) - $start) / 1e6;
$start = hrtime(true);
$total = count($values);
for ($i = 0; $i < $total; $i++) { }
$hoisted = (hrtime(true) - $start) / 1e6;
printf("recounted: %6.2fms, hoisted: %6.2fms%s", $recounted, $hoisted, PHP_EOL);
var_dump($hoisted <= $recounted);
Open this example in the editor and change it. Nothing you type is saved.
PHP's count() on an array is O(1), so this one is small - but it is free to fix, and the same pattern with strlen on a growing string, or a method call that walks a tree, is not small at all. Better still, use foreach, which has neither problem.
Strings and arrays add up
<?php
declare(strict_types=1);
$parts = array_fill(0, 30000, 'segment');
$start = hrtime(true);
$out = '';
foreach ($parts as $part) {
$out .= $part;
}
$concatenated = (hrtime(true) - $start) / 1e6;
$start = hrtime(true);
$out = implode('', $parts);
$imploded = (hrtime(true) - $start) / 1e6;
printf("concatenated: %6.2fms%s", $concatenated, PHP_EOL);
printf("imploded: %6.2fms%s", $imploded, PHP_EOL);
var_dump($imploded < $concatenated);
Open this example in the editor and change it. Nothing you type is saved.
PHP's .= is better than it used to be - the engine reallocates in blocks rather than copying every time - but implode still wins and says what it means.
The array equivalent is building by merging, which is quadratic:
<?php
declare(strict_types=1);
$chunks = array_fill(0, 2000, [1, 2, 3]);
$start = hrtime(true);
$out = [];
foreach ($chunks as $chunk) {
$out = array_merge($out, $chunk); // copies everything so far
}
$merged = (hrtime(true) - $start) / 1e6;
$start = hrtime(true);
$out = [];
foreach ($chunks as $chunk) {
foreach ($chunk as $value) {
$out[] = $value; // appends in place
}
}
$appended = (hrtime(true) - $start) / 1e6;
$start = hrtime(true);
$out = array_merge(...$chunks); // one call, one allocation
$spread = (hrtime(true) - $start) / 1e6;
printf("merge in loop: %7.2fms%s", $merged, PHP_EOL);
printf("append: %7.2fms%s", $appended, PHP_EOL);
printf("merge spread: %7.2fms%s", $spread, PHP_EOL);
var_dump($appended < $merged, $spread < $merged);
Open this example in the editor and change it. Nothing you type is saved.
array_merge inside a loop is the PHP version of the spread-into-reduce bug, and it has the same cost curve: fine for ten chunks, ruinous for ten thousand.
Memory, and when to stream
<?php
declare(strict_types=1);
function eager(int $n): array
{
$out = [];
for ($i = 0; $i < $n; $i++) {
$out[] = ['id' => $i, 'label' => 'row ' . $i];
}
return $out;
}
function lazy(int $n): Generator
{
for ($i = 0; $i < $n; $i++) {
yield ['id' => $i, 'label' => 'row ' . $i];
}
}
$before = memory_get_usage();
$rows = eager(100000);
$arrayBytes = memory_get_usage() - $before;
unset($rows);
$before = memory_get_usage();
$highWater = 0;
foreach (lazy(100000) as $row) {
// Current usage DURING the loop, not the peak - the peak still
// remembers the array above, which is the measurement mistake
// this example is here to avoid.
$highWater = max($highWater, memory_get_usage() - $before);
}
printf("array: %s bytes%s", number_format($arrayBytes), PHP_EOL);
printf("generator: %s bytes at its worst%s", number_format($highWater), PHP_EOL);
var_dump($arrayBytes > 1000000, $highWater < 10000);
Open this example in the editor and change it. Nothing you type is saved.
A PDO statement is itself iterable, so foreach ($statement as $row) streams the result set rather than buffering it - which is the difference between exporting a million rows and running out of memory.
The settings that matter more than your code
Three configuration choices outweigh most micro-optimisation:
- OPcache. Without it, every request recompiles every file. With it, the compiled bytecode is reused. It is on by default in production builds and turning it off is the single most expensive mistake available.
opcache.preloadcompiles your framework's hot classes once at start-up.- The JIT helps compute-heavy code and does very little for typical request handling, which is dominated by I/O. Measure before enabling it.
<?php
declare(strict_types=1);
printf("opcache loaded: %s%s",
extension_loaded('Zend OPcache') ? 'yes' : 'no', PHP_EOL);
printf("memory limit: %s%s", ini_get('memory_limit'), PHP_EOL);
printf("peak usage: %s bytes%s", number_format(memory_get_peak_usage(true)), PHP_EOL);
Open this example in the editor and change it. Nothing you type is saved.
A worked example
<?php
declare(strict_types=1);
$pdo = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT)');
$pdo->exec('CREATE TABLE lessons (id INTEGER PRIMARY KEY, course_id INTEGER, title TEXT, minutes INTEGER)');
$pdo->beginTransaction();
$insertCourse = $pdo->prepare('INSERT INTO courses (id, title) VALUES (?, ?)');
$insertLesson = $pdo->prepare('INSERT INTO lessons (course_id, title, minutes) VALUES (?, ?, ?)');
for ($c = 1; $c <= 50; $c++) {
$insertCourse->execute([$c, 'Course ' . $c]);
for ($l = 1; $l <= 12; $l++) {
$insertLesson->execute([$c, 'Lesson ' . $c . '.' . $l, 5 + ($l % 6)]);
}
}
$pdo->commit();
/** The slow version: a query per course, a formatter per row. */
function naive(PDO $pdo): array
{
$out = [];
foreach ($pdo->query('SELECT id, title FROM courses') as $course) {
$statement = $pdo->prepare(
'SELECT COUNT(*) AS n, SUM(minutes) AS m FROM lessons WHERE course_id = ?');
$statement->execute([$course['id']]);
$totals = $statement->fetch();
$formatter = new NumberFormatter('en_GB', NumberFormatter::DECIMAL);
$out[] = $course['title'] . ': ' . $formatter->format((int) $totals['n'])
. ' lessons, ' . $formatter->format((int) $totals['m']) . ' minutes';
}
return $out;
}
/** The fixed version: two queries, one formatter, append rather than merge. */
function batched(PDO $pdo): array
{
$totals = [];
foreach ($pdo->query(
'SELECT course_id, COUNT(*) AS n, SUM(minutes) AS m FROM lessons GROUP BY course_id'
) as $row) {
$totals[(int) $row['course_id']] = ['n' => (int) $row['n'], 'm' => (int) $row['m']];
}
$formatter = new NumberFormatter('en_GB', NumberFormatter::DECIMAL);
$out = [];
foreach ($pdo->query('SELECT id, title FROM courses') as $course) {
$total = $totals[(int) $course['id']] ?? ['n' => 0, 'm' => 0];
$out[] = $course['title'] . ': ' . $formatter->format($total['n'])
. ' lessons, ' . $formatter->format($total['m']) . ' minutes';
}
return $out;
}
$start = hrtime(true);
$slow = naive($pdo);
$naiveMs = (hrtime(true) - $start) / 1e6;
$start = hrtime(true);
$fast = batched($pdo);
$batchedMs = (hrtime(true) - $start) / 1e6;
printf("naive: %7.2fms, 51 queries%s", $naiveMs, PHP_EOL);
printf("batched: %7.2fms, 2 queries%s", $batchedMs, PHP_EOL);
printf("same result: %s%s", $slow === $fast ? 'yes' : 'no', PHP_EOL);
printf("speed-up: %.1fx%s", $naiveMs / max($batchedMs, 0.001), PHP_EOL);
echo PHP_EOL, $fast[0], PHP_EOL, $fast[49], PHP_EOL;
Open this example in the editor and change it. Nothing you type is saved.
The two functions return identical output. One makes fifty-one queries and constructs fifty formatters; the other makes two and constructs one. That is the whole lesson, and on a real database over a network the gap is a hundred times wider than it looks here.
Note the transaction around the inserts: six hundred inserts committed individually would each be a separate durability fence. Wrapping a bulk load in one transaction is the other order-of-magnitude fix in this area.
When it goes wrong
| Symptom | Cause |
|---|---|
| A page is slow in proportion to the rows shown | N+1 queries; batch and index in PHP |
| A bulk insert takes minutes | No transaction around it |
| Memory exhausted on a large export | Buffering the whole result; stream with a generator |
| A loop got slower as it ran | array_merge or string copying inside it |
| An object is constructed thousands of times | Hoist it out of the loop |
| Production is slower than development | OPcache disabled, or a different configuration |
| An optimisation made no difference | It was not the bottleneck; profile first |
SELECT * is slow |
Fetching columns nobody reads, including large text |
A check you can run
Turn on query logging for one page of your application - PDO can be wrapped, or the database can log - and count the queries.
If the number changes when you add a row to the page, you have an N+1. That single test finds more real latency than any amount of reading about micro-optimisation, and the fix is always the same: collect the ids, one query, index by id.