Skip to content
intermediate Phase 77 · Performance Fundamentals

N+1 Query Problem

45m
1 problems
Topic Progress 0%

Detecting N+1 Queries

Enable Query Logging

// app/etc/env.php
return [
    'db' => [
        'logger' => [
            'enabled' => true,
            'log-file' => 'var/log/db.log',
        ],
    ],
];

Identify N+1 Pattern

// BAD: N+1 query problem
$products = $this->productCollectionFactory->create();
$products->load();

foreach ($products as $product) {
    // This triggers a separate query for EACH product
    $category = $this->categoryFactory->create()->load($product->getCategoryId());
    echo $category->getName();
}
// Result: 1 query for products + N queries for categories

Debug N+1 Queries

use Magento\Framework\DB\Logger\File as QueryLogger;

class N1Detector
{
    private $queryCount = 0;
    private $queries = [];

    public function detect($collection)
    {
        $this->queryCount = 0;
        $this->queries = [];

        // Log queries
        $logger = $this->objectManager->get(QueryLogger::class);

        foreach ($collection as $item) {
            $this->queryCount++;
            // Access related data
            $item->getCategory();
        }

        if ($this->queryCount > 10) {
            $this->logger->warning('Potential N+1 query', [
                'query_count' => $this->queryCount,
                'collection_size' => $collection->getSize(),
            ]);
        }
    }
}

Key Points

  • N+1 = 1 query for collection + N queries for related data
  • Enable query logging to detect patterns
  • Look for loops that trigger database queries
  • Use query count to identify issues

Preventing N+1 Queries

Join Strategy

// GOOD: Use JOIN to fetch related data
$collection = $this->productCollectionFactory->create();
$collection->getSelect()->join(
    ['c' => 'catalog_category_entity'],
    'main_table.category_id = c.entity_id',
    ['category_name' => 'name']
);

// Single query with JOIN
foreach ($collection as $product) {
    echo $product->getData('category_name');
}

Subselect Strategy

// GOOD: Use subselect for related data
$collection = $this->productCollectionFactory->create();
$collection->getSelect()->joinLeft(
    ['cpv' => $this->resource->getTable('catalog_product_entity_varchar')],
    'cpv.entity_id = main_table.entity_id AND cpv.attribute_id = ?',
    ['name_value']
)->where('cpv.attribute_id = ?', 71);

// Or use addFieldToJoin
$collection->addFieldToJoin(
    'name',
    'catalog_product_entity_varchar',
    'entity_id',
    ['attribute_id' => 71]
);

Batch Loading

// GOOD: Batch load related data
$products = $this->productCollectionFactory->create();
$productIds = $products->getAllIds();

// Load all categories in one query
$categories = $this->categoryFactory->create()->getCollection()
    ->addFieldToFilter('entity_id', ['in' => $productIds])
    ->load();

$categoriesMap = [];
foreach ($categories as $category) {
    $categoriesMap[$category->getId()] = $category;
}

// Use map instead of loading in loop
foreach ($products as $product) {
    $category = $categoriesMap[$product->getCategoryId()] ?? null;
}

Key Points

  • Use JOINs for related data
  • Use subselects for complex relationships
  • Batch load data before loops
  • Avoid loading in foreach loops

Eager Loading

Magento Collection Loading

// Eager loading with addAttributeToSelect
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect(['name', 'price', 'sku', 'description']);
$collection->load();

// Join related tables
$collection->joinTable(
    'catalog_product_index_price',
    'entity_id=entity_id',
    ['price', 'tax_class_id']
);

Lazy vs Eager Loading

// LAZY: Load on access (N+1 risk)
$product = $this->productFactory->create();
$product->load($id);  // Single query
$product->getDescription();  // May trigger another query

// EAGER: Load all at once
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect('*');  // All attributes
$collection->load();  // Single query with JOINs

Custom Eager Loading

namespace Vendor\Module\Model\ResourceModel\Product\Collection;

class EagerCollection extends \Magento\Catalog\Model\ResourceModel\Product\Collection
{
    public function __construct(
        \Magento\Framework\DataObject\Factory $dataObjectFactory,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        \Magento\Eav\Model\Entity\Table\Collection $tableCollection,
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
        array $data = []
    ) {
        parent::__construct($dataObjectFactory, $storeManager, $tableCollection);
        $this->_init($productCollectionFactory);
    }

    public function addEagerAttributes()
    {
        $this->addAttributeToSelect([
            'name',
            'sku',
            'price',
            'description',
            'short_description',
            'image',
            'small_image',
            'thumbnail',
        ]);
        return $this;
    }
}

Key Points

  • Eager loading fetches related data upfront
  • Use addAttributeToSelect for specific attributes
  • Join related tables in collection
  • Balance memory vs query count

Collection Query Optimization

Optimize Collection Queries

// BAD: Load entire collection
$collection = $this->productCollectionFactory->create();
$collection->load();  // Loads all products

// GOOD: Use pagination
$collection = $this->productCollectionFactory->create();
$collection->setPageSize(20);
$collection->setCurPage(1);
$collection->load();

// GOOD: Select specific fields
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect(['name', 'price', 'sku']);
$collection->load();

// GOOD: Filter early
$collection = $this->productCollectionFactory->create();
$collection->addFieldToFilter('status', 1);
$collection->load();

Collection Count vs Load

// Check count without loading
$count = $collection->getSize();  // Uses SQL COUNT

// Get IDs without loading objects
$ids = $collection->getAllIds();  // Faster than load()

// Load only what you need
$collection->load();
foreach ($collection as $item) {
    // Process item
}

Debug Collection Queries

// Get generated SQL
$sql = $collection->getSelect()->__toString();
echo $sql;

// Count queries
$profiler = new \Magento\Framework\Profiler();
$profiler->start('collection_load');
$collection->load();
$profiler->stop('collection_load');

Key Points

  • Use setPageSize for large collections
  • Select only needed attributes
  • Filter early to reduce result set
  • Use getSize() instead of count()

Practice Problems

0 / 1 solved
Fix N+1 Query

Identify and fix an N+1 query problem in a product listing.

Solution 
// Solution 1: Use JOIN
$collection = $this->productCollectionFactory->create();
$collection->joinField(
    'qty',
    'cataloginventory_stock_item',
    'qty',
    'product_id=entity_id',
    ['is_in_stock' => 1],
    'left'
);
$collection->load();

// Solution 2: Batch loading
$products = $this->productCollectionFactory->create();
$productIds = $products->getAllIds();

$stockItems = $this->stockRegistry->getStockItems($productIds);
$stockMap = [];
foreach ($stockItems as $stock) {
    $stockMap[$stock->getProductId()] = $stock;
}

foreach ($products as $product) {
    $stock = $stockMap[$product->getId()] ?? null;
    echo $product->getName() . ': ' . ($stock ? $stock->getQty() : 0);
}

Quiz

1. What is the N+1 query problem?

Question 1 options

2. How to prevent N+1 queries?

Question 2 options

3. What is eager loading?

Question 3 options

4. Why use getSize() instead of count()?

Question 4 options

Flashcards

Question

N+1 problem?

Click to reveal answer

Answer

1 query for collection + N queries in loop

Question

Prevent N+1?

Click to reveal answer

Answer

Use JOINs or batch loading

Question

Eager loading?

Click to reveal answer

Answer

Fetch related data upfront in one query

Question

getSize() benefit?

Click to reveal answer

Answer

Uses SQL COUNT, faster than loading all

Revision Notes

Key Takeaways

  • 1. N+1 = 1 query for collection + N queries for related data
  • 2. Use JOINs to fetch related data in one query
  • 3. Batch load related data before loops
  • 4. Use getSize() for count, getAllIds() for IDs

Interview Tips

  • • Explain the N+1 problem with examples
  • • Discuss prevention strategies
  • • Know when to use eager vs lazy loading

Cheat Sheet

N+1 Query Problem

  • Detection: query logging
  • Prevention: JOINs, batch loading
  • Eager: addAttributeToSelect
  • Optimize: setPageSize, filter early