Performance profiling and N+1 query hunting

And fixing the usual suspect, the N+1 query pattern. Guessing is expensive. Profiling tells you where time actually goes.

Measure first

Enable query logging in development. A simple PDO wrapper counts statements per request:

final class LoggingPdo extends PDO
{
    public static int $queryCount = 0;

    public function query(string $query, ?int $fetchMode = null, ...$args): PDOStatement|false
    {
        self::$queryCount++;
        error_log('[SQL] ' . $query);
        return parent::query($query, $fetchMode, ...$args);
    }
}

At the end of the request, log the total. If a list page runs 47 queries for 10 rows, you have work to do.

What N+1 looks like

// 1 query for posts
$posts = $pdo->query('SELECT * FROM posts LIMIT 10')->fetchAll();

// N queries - one per post for the author
foreach ($posts as &$post) {
    $stmt = $pdo->prepare('SELECT name FROM users WHERE id = ?');
    $stmt->execute([$post['user_id']]);
    $post['author'] = $stmt->fetchColumn();
}

Ten posts, eleven queries. A thousand posts, a thousand and one. The fix is almost always eager loading or a JOIN.

Fix with JOIN

SELECT p.*, u.name AS author_name
FROM posts p
INNER JOIN users u ON u.id = p.user_id
LIMIT 10

Fix with WHERE IN

When a JOIN gets messy (multiple relations), load IDs in bulk:

$userIds = array_unique(array_column($posts, 'user_id'));
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE id IN ($placeholders)");
$stmt->execute(array_values($userIds));
$users = [];
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $users[$row['id']] = $row['name'];
}
foreach ($posts as &$post) {
    $post['author'] = $users[$post['user_id']] ?? 'Unknown';
}

Two queries total. Same result, fraction of the round trips.

Xdebug profiling (local)

Install Xdebug, enable the profiler for one request (XDEBUG_TRIGGER=1 in the query string or cookie). Open the output .cachegrind file in KCacheGrind or QCacheGrind. Look for the widest bars – that is where time went.

Do not leave the profiler on always; it slows everything down. Trigger it when a page feels wrong.

Other common wins

• Missing indexes on WHERE / JOIN columns (see MySQL intermediate tutorials)
• Loading full tables when you need three columns
• Autoloading huge object graphs you never use
• Repeated file_exists or remote HTTP inside loops
• Logging at DEBUG in production

Production without Xdebug

Use application timing logs, APM tools if the budget allows, or MySQL’s slow query log. Sample requests – do not log every query in production unless you enjoy filling disks.

Fix the biggest number first. A 200 ms query beat into shape beats micro-optimising string concatenation in a loop nobody hits.