Case study: one GraphQL query that issued 1,900 SQL queries

Field notes — a real production bug, project name withheld. Symptom → investigation → fix → lesson.

The symptom

A headless category page took 11 to 14 seconds to load. The GraphQL query behind it asked for 48 products with names, prices, and a custom stock_status field. On staging with 12 products per page it felt fine at under a second. On production with the real page size, the same query timed out behind the CDN on mobile networks, and the storefront team assumed the problem was the network or the CDN cache rules. Raising the PHP-FPM timeout from 30 to 60 seconds only moved the failure: the page now loaded, but slowly enough that the bounce rate on category pages doubled.

The telling detail was that the REST equivalent of the same page was fast. Loading the category through the traditional product list block took about 900 milliseconds. Only the GraphQL path was slow, and only when the custom stock_status attribute was included. Removing that one field from the query dropped the response time from 12 seconds to under 800 milliseconds. The field was the whole difference.

Investigation

We enabled the MySQL slow-query log and the Magento DB profiler for a single GraphQL request and counted. One category query with 48 products issued 1,904 SQL statements. The query log showed the same three statements repeating once per product:

SELECT `s`.* FROM `inventory_source_item` AS `s`
 WHERE `s`.`sku` = 'SKU-0001' AND `s`.`source_code` = 'default';
SELECT `p`.* FROM `catalog_product_entity_decimal` AS `p`
 WHERE `p`.`entity_id` = 10241 AND `p`.`attribute_id` = 77;
SELECT `w`.* FROM `cataloginventory_stock_item` AS `w`
 WHERE `w`.`product_id` = 10241;

The pattern repeated 48 times with only the SKU and entity id changing. This was a textbook N+1 fanout: the top-level products resolver loaded the collection in one query, but the custom stock_status resolver loaded its data per product, per request, with three queries each. At 48 products that is 144 queries for stock alone; with two store views and child-product expansion for configurables, the real count reached 1,900.

The resolver code showed why. It had been written as a straightforward attribute resolver:

public function resolve(Field $field, $context, ResolveInfo $info, array $value = null, array $args = null)
{
    $product = $this->productRepository->getById($value['entity_id']);
    $sourceItem = $this->sourceItemRepository->get($product->getSku(), 'default');
    $stockItem = $this->stockRegistry->getStockItem($product->getId());
    return $this->formatter->format($sourceItem, $stockItem);
}

Three repository calls per product, each issuing its own SQL, each bypassing the collection the parent resolver had already loaded. The productRepository->getById call alone reloaded a product model the parent already had, discarding the already-fetched row and issuing a fresh SELECT plus EAV joins. Worse, none of it was cached: the resolver ran inside a request-scoped GraphQL execution with no memoization, so two sibling products sharing nothing still each paid the full cost, and configurable children multiplied it further.

We confirmed the multiplier by testing page sizes. At 12 products the request issued 480 queries and took 2.8 seconds. At 24 it issued 960 and took 6 seconds. At 48 it issued 1,900 and took 12 seconds. Perfectly linear, which ruled out locks or slow joins and pointed squarely at per-item work. The database CPU graph showed the same linearity: each additional product added a fixed cost, and under concurrent category traffic the database spent more time serving duplicated stock lookups than serving real checkout writes.

A secondary issue hid behind the first. The resolver called getStockItem through the legacy cataloginventory API even though the project ran multi-source inventory, so every call loaded both the MSI source item and the legacy stock row and then reconciled them in PHP. Half the queries were for a table whose values were no longer authoritative.

Fix

The fix replaced per-product repository calls with a single batched load. Magento GraphQL supports batch resolvers for exactly this case: instead of resolving each product independently, the batch path collects all entity ids first and loads them in one query.

public function resolveBatched(array $products): array
{
    $ids = array_column($products, 'entity_id');
    $skus = array_column($products, 'sku');

    $sourceItems = $this->sourceItemCollection->loadBySkus($skus);
    $stockItems = $this->stockCollection->loadByProductIds($ids);

    $result = [];
    foreach ($products as $product) {
        $result[$product['entity_id']] = $this->formatter->format(
            $sourceItems[$product['sku']] ?? null,
            $stockItems[$product['entity_id']] ?? null
        );
    }
    return $result;
}

Two queries replaced 1,900: one WHERE sku IN (...) against the source items and one WHERE product_id IN (...) against the stock table. The redundant productRepository->getById call was deleted entirely since the parent resolver already supplies the product row. The legacy cataloginventory_stock_item lookup was dropped in favor of the MSI source, removing the double-read.

We also set a query-complexity guard at the gateway: category queries over 50 products now require pagination, and the New Relic alert that previously watched only average response time now watches SQL statements per GraphQL operation. Any operation issuing more than 200 statements is flagged for review before it can grow back into a fanout.

The result was immediate. The same 48-product query went from 1,904 statements and 12 seconds to 9 statements and 620 milliseconds. Database CPU during peak category traffic dropped by a third, and the CDN timeout errors on category pages disappeared without any timeout increase.

Lesson

In GraphQL, every field resolver is a loop body, and anything it loads per item is multiplied by page size. A resolver that looks cheap in isolation becomes a 1,900-query storm at 48 products, and staging never catches it because staging uses small pages. Review custom resolvers by statements-per-operation, not by single-product timing: enable the DB profiler, run the query at production page size, and count. If the statement count grows with the page size, the resolver needs batching, not caching. Load collections with IN queries, reuse what the parent resolver already fetched instead of reloading it by id, and cap page sizes so one query cannot linearly scale its way into an outage.

Illustrated in magento2-graphql-resolver-batching.