Magento 2 loading slowly – N+1 problem and collections without limit

The category page took 18 seconds to load with 200 products. PageSpeed score: 12/100. The problem was not the server or Varnish – it was in a module’s code that executed a separate SQL query for every product.

Symptoms

• TTFB (Time To First Byte) above 10 seconds
• Category page load time grows linearly with the number of products
• MySQL slow query log full of queries like SELECT * FROM catalog_product_entity WHERE entity_id = X
• Problem only on product list pages, not on product pages

Diagnosis

# Enable MySQL slow query log
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 0.1

# Or use Xdebug profiler
# xdebug.mode = profile
# Open category page with XDEBUG_PROFILE=1
# Analyse cachegrind - find the function called 200+ times

Cause

A “related products” module loaded a separate collection of relations for each product in the list. With 200 products = 200 additional SQL queries. Classic N+1 problem.

Solution

<?php
// Before - N+1: one query per product
foreach ($products as $product) {
    $related = $this->relatedFactory->create()
        ->addFieldToFilter('parent_id', $product->getId())
        ->load(); // SQL QUERY for each product!
}

// After - one query for all
$productIds = array_map(fn($p) => $p->getId(), $products);
$allRelated = $this->relatedFactory->create()
    ->addFieldToFilter('parent_id', ['in' => $productIds])
    ->setPageSize(1000)
    ->load();

$grouped = [];
foreach ($allRelated as $rel) {
    $grouped[$rel->getParentId()][] = $rel;
}

Result

Category page load time: from 18s to 1.2s. SQL query count: from 215 to 8. PageSpeed: from 12 to 78.