PHP API Performance: N+1 Queries, cURL, and Connection Pools

Before anything else: I can't show you the client's actual code. NDA, confidentiality, the usual reasons that come with doing custom PHP work for cybersecurity firms and their clients for a living. Every code sample in this article is reconstructed from memory and rewritten from scratch to demonstrate the same pattern, not copied from anything I was paid to keep private. If it reads like a hundred other PHP codebases you've inherited, that's not a coincidence.

The message that started this engagement: "Our API times out under load. Can you look at it."

That sentence is doing a lot of quiet work. It sounds like one problem. It is never one problem. "Slow" is what five separate mistakes sound like when they're all screaming from inside the same trench coat, and the client's job is to notice the coat is moving. My job was to find out how many people were inside it.

If you've read my complaints about the PHP tutorial ecosystem, you already know where this is going. The original developer wasn't bad at his job. He built something that worked for the load the business had at the time. The business grew. The code didn't.

What "Slow" Means

Before you touch a line of code, you need a mental model of where time goes in a single request, because "the API is slow" without that model is a feeling, and you can't fix a feeling with a git commit.

A request to a PHP API spends time in roughly this order:

1. Network round trip to your server.
2. Waiting for a PHP-FPM worker to become available.
3. Bootstrapping the framework or application (autoloading, config, routing).
4. Talking to the database, possibly more than once.
5. Talking to any external service, possibly more than once.
6. Serializing the response.
7. Network round trip back to the client.

Every one of those seven steps has a latency budget. Add them up and you get your total response time. The client's dashboard endpoint was taking four to six seconds under moderate load. A user request has no patience for that math, and neither does a load balancer's timeout setting.

I didn't guess where the time went. I measured it, with the crudest tool available: microtime(true) calls bracketing each stage, logged to a file during a controlled load test, because the client's hosting didn't have Blackfire or Xdebug's profiler configured and I wasn't going to spend the first day of a paid engagement fighting with tooling installation on production. Sometimes the sophisticated profiler is the right call. Sometimes four microtime() calls and a spreadsheet get you the answer by lunch.

$t0 = microtime(true);
$data = fetchDashboardData($userId);
$t1 = microtime(true);
$enriched = enrichWithExternalData($data);
$t2 = microtime(true);
$response = json_encode($enriched);
$t3 = microtime(true);

error_log(sprintf(
    "fetch=%.3fs enrich=%.3fs encode=%.3fs",
    $t1 - $t0, $t2 - $t1, $t3 - $t2
));

The log output told the whole story before I'd read a single line of business logic:

fetch=0.412s enrich=3.891s encode=0.203s

enrichWithExternalData was the villain, by a mile. Everything else was, comparatively, fine.

The N+1 Query

Inside fetchDashboardData, the pattern looked something like this:

function fetchDashboardData(int $userId): array {
    $orders = $db->query("SELECT * FROM orders WHERE user_id = {$userId}");
    $result = [];

    foreach ($orders as $order) {
        $items = $db->query(
            "SELECT * FROM order_items WHERE order_id = {$order['id']}"
        );
        $order['items'] = $items;
        $result[] = $order;
    }

    return $result;
}

That loop runs one query to fetch orders, then one additional query per order to fetch its items. For a user with three orders, that's four queries. For a user with three hundred orders, because they'd been a customer since the beginning and the business never purged old data, that's three hundred and one queries, each paying the full round-trip cost to MySQL. This is the N+1 problem. It's invisible in development because your test data has five orders and feels instant. Your best customer's five hundred orders will find it in production.

The fix isn't clever. It's a single join, pulling everything in one round trip and reassembling it in PHP:

function fetchDashboardData(int $userId): array {
    $rows = $db->query("
        SELECT o.*, oi.id AS item_id, oi.sku, oi.quantity
        FROM orders o
        LEFT JOIN order_items oi ON oi.order_id = o.id
        WHERE o.user_id = ?
    ", [$userId]);

    $orders = [];
    foreach ($rows as $row) {
        $orderId = $row['id'];
        if (!isset($orders[$orderId])) {
            $orders[$orderId] = [
                'id' => $orderId,
                'total' => $row['total'],
                'items' => [],
            ];
        }
        if ($row['item_id'] !== null) {
            $orders[$orderId]['items'][] = [
                'sku' => $row['sku'],
                'quantity' => $row['quantity'],
            ];
        }
    }

    return array_values($orders);
}

One query, every time, regardless of order count. Also worth noticing: I switched from string-interpolated SQL to a parameterized query with a bound placeholder. That change had nothing to do with speed. It closed off a SQL injection risk before I left the codebase. If you've never sat with why that distinction matters at the protocol level, what SQL injection is is worth your time.

Related reading: CVE-2026-31431 — [add link]

The Serial External Calls

enrichWithExternalData was where the real damage lived. The dashboard needed pricing data from a third-party vendor API, one call per item, and the original code did this:

function enrichWithExternalData(array $orders): array {
    foreach ($orders as &$order) {
        foreach ($order['items'] as &$item) {
            $item['currentPrice'] = fetchVendorPrice($item['sku']);
        }
    }
    return $orders;
}

function fetchVendorPrice(string $sku): float {
    $ch = curl_init("https://vendor.example.com/price/{$sku}");
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);
    return json_decode($response, true)['price'];
}

Each curl_exec call blocks until it gets a response. If the vendor API takes 150 milliseconds to answer, and a user's dashboard has twenty items across their order history, you're looking at twenty sequential 150-millisecond waits, back to back, adding up to three full seconds of a single PHP process doing nothing but waiting on a network socket. The bottleneck sits one layer up from the database or the code itself, in the architecture: doing serially what should happen concurrently.

curl_multi exists for exactly this situation, and it's been part of PHP for a long time, which makes it more frustrating that it shows up in production code this rarely:

function fetchVendorPricesConcurrently(array $skus): array {
    $multiHandle = curl_multi_init();
    $handles = [];

    foreach ($skus as $sku) {
        $ch = curl_init("https://vendor.example.com/price/{$sku}");
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_TIMEOUT, 2);
        curl_multi_add_handle($multiHandle, $ch);
        $handles[$sku] = $ch;
    }

    $running = null;
    do {
        curl_multi_exec($multiHandle, $running);
        curl_multi_select($multiHandle);
    } while ($running > 0);

    $prices = [];
    foreach ($handles as $sku => $ch) {
        $response = curl_multi_getcontent($ch);
        $prices[$sku] = json_decode($response, true)['price'] ?? null;
        curl_multi_remove_handle($multiHandle, $ch);
        curl_close($ch);
    }

    curl_multi_close($multiHandle);
    return $prices;
}

Twenty requests fired at once instead of twenty requests fired one after another. The total wait time drops from "sum of every request" to close to "the slowest single request," because they're all in flight simultaneously. That one change took enrich from 3.891 seconds down to a shade over 200 milliseconds in the load test. Same vendor, same network, same data. The only thing that changed was whether the code waited for each answer before asking the next question.

I also added a two-second timeout on each handle, because a hanging request to a slow vendor endpoint had, before this fix, been capable of hanging the entire dashboard until a user gave up and refreshed the page. An API you don't control should never get an unbounded amount of your users' patience.

No Connection Reuse

Smaller contributor, still worth fixing: every incoming request opened a brand new connection to MySQL, negotiated a fresh handshake, authenticated, and tore it all down at the end of the request. Under load, that connection overhead stacks up and starts competing with max_connections on the database server itself, which is how you end up with an API that's slow for a different reason entirely.

Two honest options here, and the choice depends on your traffic shape more than your personal preference. PDO::ATTR_PERSISTENT reuses connections within the same PHP-FPM worker pool, which helps, but persistent connections carry their own sharp edges: leftover transaction state from a previous request bleeding into the next one if you're not careful about resetting session state, and a ceiling on how many persistent connections your database can hold open. The other option, and the one I recommended here, was putting a connection pooler like ProxySQL in front of MySQL, letting PHP open lightweight connections to the pooler while the pooler maintains a smaller, reused pool of real connections to the database. More moving parts to operate, less risk of a subtle bug from stale connection state. For this client's traffic pattern, the operational cost was worth it.

Recomputing What Doesn't Change

The last piece, and the one that felt almost embarrassing once I saw it: the dashboard recalculated a set of aggregate statistics, order totals over time, category breakdowns, on every single request, even though the underlying data only changed a handful of times per day for most users.

This is what caching is for, and it's the kind of fix that feels like cheating because it's so much less code than everything above it:

function getDashboardStats(int $userId): array {
    $cacheKey = "dashboard_stats:{$userId}";
    $cached = $redis->get($cacheKey);

    if ($cached !== false) {
        return json_decode($cached, true);
    }

    $stats = computeExpensiveStats($userId);
    $redis->setex($cacheKey, 300, json_encode($stats));

    return $stats;
}

Five minutes of staleness on numbers that update a few times a day is not a meaningful trade-off for most dashboards, and it turned an expensive aggregation query into a Redis lookup for the majority of requests. If you haven't worked with it directly, what Redis is and why it fits this exact job is worth the detour.

Related reading: [add thematic link — e.g. connection pooling or caching-adjacent piece]

What "Fast Now" Cost

Total time across all four fixes: about a week and a half, most of it spent on the connection pooling change because that one touched infrastructure the client's ops team had to sign off on, not application code alone. The N+1 fix and the caching layer were each closer to a day, once the profiling had already pointed at them.

Response time on the dashboard endpoint went from four to six seconds under load down to under 300 milliseconds for the cached path and around 500 milliseconds for a cold cache miss. Four unremarkable fixes, stacked together, did more than any single clever one would have.

What this doesn't cover: read replicas or horizontal database scaling, async PHP runtimes like Swoole or RoadRunner, setting up production-grade profiling (Blackfire, Xdebug) rather than the manual microtime() approach used here, and circuit-breaker patterns for the vendor API beyond a flat timeout. Any of those might be the right next step for a system that outgrows this fix — they weren't needed here.

You won't hear this part in a conference talk. There's rarely a single villain. There's a stack of small, boring decisions, each one reasonable in isolation, each one made by someone doing their best under the constraints they had at the time, that add up to a system that stops working the moment real traffic shows up. If you're building your own API from the ground up rather than inheriting one, rolling your own custom API and designing it with a proper MVC structure from day one will save you most of this pain later. But if you've already inherited the mess, the fix isn't a rewrite. It's four microtime() calls and the discipline to read what they tell you.

Code samples above are reconstructed and rewritten from scratch to illustrate the pattern — not reproduced from the client's original codebase, per the confidentiality note at the top of this piece. Further reading: the PHP manual's curl_multi_exec documentation, PDO's persistent connections documentation, and the ProxySQL documentation.