Magento 2 Product Relations Performance: Fixing Upsells, Crosssells & Related Products at Scale

Every Magento 2 product detail page loads three types of product relations: upsells, crosssells, and related products. On a small catalog, you barely notice them. On a large catalog with thousands of products and complex rule-based relations, they become one of the most expensive parts of your product page — and one of the hardest to debug.

This guide breaks down how Magento 2 loads product relations, where the performance bottlenecks hide, and what you can do to keep your product pages fast even when every product has dozens of relations.

How Magento 2 Loads Product Relations

Magento 2 stores product relations in the catalog_product_link table, along with the catalog_product_link_type and catalog_product_link_attribute tables. The three relation types — related (1), upsell (2), and crosssell (3) — are all stored in the same structure.

When a customer opens a product detail page, Magento goes through this sequence:

1. Load the main product via the product repository
2. Fetch all links for the product from catalog_product_link where product_id = <current_product>
3. Load each linked product as a full product entity (EAV load)
4. Apply pricing — including tier prices, catalog price rules, and tax calculations
5. Load product images for each relation (gallery API)
6. Apply inventory status — check stock for each related product
7. Render the relation blocks (related products list, upsell block, crosssell block)

Steps 3–6 are where things go wrong. Each linked product triggers a full EAV load, a price calculation, and an inventory check. If a product has 20 related products, that's 20 separate EAV queries, 20 price calculations, and 20 stock checks — most of which happen inside loops that Magento doesn't batch.

The N+1 Problem in Product Relations

The core issue is an N+1 query pattern. Magento loads the link collection (1 query), then loads each linked product individually (N queries). On a product with 15 related products, that's 16 queries minimum — and in practice, many more when you factor in EAV attributes, pricing, and inventory.

Here's what the query log looks like on a typical PDP with 12 related products:

SELECT * FROM catalog_product_link WHERE product_id = 1234;
-- 12 rows returned

-- For each linked product (×12):
SELECT * FROM catalog_product_entity WHERE entity_id = <link_id>;
SELECT * FROM catalog_product_entity_varchar WHERE entity_id = <link_id>;
SELECT * FROM catalog_product_entity_decimal WHERE entity_id = <link_id>;
SELECT * FROM catalog_product_index_price WHERE entity_id = <link_id>;
SELECT * FROM cataloginventory_stock_status WHERE product_id = <link_id>;

That's roughly 60+ queries just for the related products block. On a high-traffic store, this adds up to significant database pressure.

Diagnosing the Problem

Before optimizing, measure the actual impact. You need to know exactly how much time your product relations are adding to the PDP.

Enable the Database Profiler

// In app/etc/env.php or a temporary diagnostic module
'profiler' => [
    'class' => \Magento\Framework\DB\Profiler::class,
    'enabled' => true,
]

Or use the built-in profiler via MAGE_PROFILER=1 in your .htaccess or Nginx config:

SetEnv MAGE_PROFILER 1

Look for query patterns that repeat for each linked product. Count the queries attributed to catalog_product_link joins and subsequent EAV loads.

Use Blackfire or New Relic

Trace a product page with 10+ relations and look for:

• The getLinkCollection() call tree
• Time spent in \Magento\Catalog\Model\Product\Link::getLinkedProductCollection
• Total EAV load time for linked products
• Price calculation overhead (getPriceInfo(), getFinalPrice())

If your relations loading takes more than 200ms, you have a problem worth fixing.

Optimization 1: Limit the Number of Loaded Relations

The simplest fix is also the most effective: load fewer relations. Magento lets you configure how many related/upsell/crosssell products to display, but the underlying collection still loads all of them before slicing.

Reduce the Link Collection Size

In your product blocks, limit the collection early — before the EAV load happens:

// In a custom block that extends \Magento\Catalog\Block\Product\ProductList\Related
protected function _prepareData()
{
    $this->_itemCollection = $this->_productFactory->create()
        ->getCollection()
        ->addAttributeToSelect(['name', 'price', 'small_image', 'short_description'])
        ->joinField(
            'link_id',
            'catalog_product_link',
            'link_id',
            'product_id=entity_id',
            '{{table}}.linked_product_id=' . $this->getProduct()->getId(),
            'inner'
        )
        ->addLinkType(\Magento\Catalog\Model\Product\Link::LINK_TYPE_RELATED)
        ->setPageSize(6)  // ← Critical: limit before EAV load
        ->setCurPage(1);

    $this->_itemCollection->addStoreFilter();
    $this->_itemCollection->addPriceData();
    $this->_itemCollection->addTaxPercents();
    $this->_itemCollection->addUrlRewrite();

    return $this;
}

The key is setPageSize(6) — this limits the SQL query itself, so Magento only loads 6 products from the database instead of loading all 20 and displaying 6.

Audit Product Relations in Bulk

If your merchandisers are adding 30+ related products per product, you have a data problem. Run this query to find the worst offenders:

SELECT
    product_id,
    link_type,
    COUNT(*) as relation_count
FROM catalog_product_link
GROUP BY product_id, link_type
HAVING relation_count > 10
ORDER BY relation_count DESC
LIMIT 50;

Any product with more than 10 relations of a single type is a candidate for cleanup. In most storefronts, 4–6 related products and 3–4 crosssells is plenty.

Optimization 2: Select Only the Attributes You Need

By default, Magento loads the full EAV entity for each linked product — every attribute, including long descriptions, gallery data, and attributes that only matter on the PDP itself. For a relation block, you only need a handful of attributes.

Use addAttributeToSelect Explicitly

$this->_itemCollection
    ->addAttributeToSelect(['name', 'price', 'small_image', 'short_description', 'url_key'])
    // Do NOT use addAttributeToSelect('*') — it loads every attribute
    ;

This alone can cut the EAV query count by 60–80% per linked product, especially on catalogs with many custom attributes.

Disable the Flat Catalog for Relation Collections

The flat catalog (catalog_product_flat) is often recommended for performance, but it creates wide tables with every attribute. When Magento loads related products via the flat table, it selects all flat columns — even if you only need 5 attributes.

For relation collections, bypass the flat catalog:

$this->_itemCollection->setStoreId($this->_storeManager->getStore()->getId())
    ->addAttributeToSelect(['name', 'price', 'small_image', 'short_description'])
    ->setFlag('disable_flat_catalog', true);  // Force EAV with limited attribute selection

With limited addAttributeToSelect, EAV is actually faster than flat for relation blocks because it only queries the specific attribute tables instead of joining the entire flat row.

Optimization 3: Cache the Rendered Relation Blocks

Product relations are ideal candidates for block-level caching because they change rarely. Magento's full page cache already handles PDP blocks, but if you're running without FPC (e.g., logged-in customers), relation blocks can bypass cache entirely.

Add Cache Key Data to Relation Blocks

// In your custom relation block
protected function getCacheKeyInfo()
{
    return [
        'PRODUCT_RELATIONS',
        $this->getProduct()->getId(),
        $this->_storeManager->getStore()->getId(),
        $this->getLinkType(),
        $this->getPageSize() ?? 6,
    ];
}

public function getCacheLifetime()
{
    return 86400; // Cache for 24 hours
}

Invalidate on Product Relation Changes

Hook into the product save event to invalidate cached relation blocks:

<!-- etc/frontend/events.xml -->
<event name="catalog_product_save_after">
    <observer name="invalidate_product_relations_cache"
              instance="Vendor\Module\Observer\InvalidateProductRelationsCache"/>
</event>

class InvalidateProductRelationsCache implements \Magento\Framework\Event\ObserverInterface
{
    private $cache;

    public function __construct(\Magento\Framework\App\CacheInterface $cache)
    {
        $this->cache = $cache;
    }

    public function execute(\Magento\Framework\Event\Observer $observer)
    {
        $product = $observer->getEvent()->getProduct();
        // Invalidate this product's relation blocks
        $this->cache->remove('PRODUCT_RELATIONS_' . $product->getId() . '_');
    }
}

Optimization 4: Preload Product Data with a Single Query

The most impactful optimization is replacing the N+1 pattern with a single batch query. Instead of loading each linked product individually, load them all in one go.

Custom Batch Loader

class BatchProductLoader
{
    private ProductRepositoryInterface $productRepository;
    private array $loadedProducts = [];

    public function __construct(
        ProductRepositoryInterface $productRepository
    ) {
        $this->productRepository = $productRepository;
    }

    public function loadProducts(array $productIds, int $storeId): array
    {
        // Filter out already-loaded products
        $neededIds = array_diff($productIds, array_keys($this->loadedProducts));

        if (empty($neededIds)) {
            return array_intersect_key($this->loadedProducts, array_flip($productIds));
        }

        $collection = $this->productRepository->create()->getCollection()
            ->addAttributeToSelect(['name', 'price', 'small_image', 'short_description', 'url_key'])
            ->addStoreFilter($storeId)
            ->addUrlRewrite()
            ->addFieldToFilter('entity_id', ['in' => $neededIds]);

        $collection->addPriceData();
        $collection->addTierPriceData();
        $collection->addTaxPercents();

        foreach ($collection as $product) {
            $this->loadedProducts[$product->getId()] = $product;
        }

        return array_intersect_key($this->loadedProducts, array_flip($productIds));
    }
}

By injecting this loader and using it in your relation blocks, you replace N individual EAV loads with one collection query that fetches all linked products at once. The price, tax, and URL rewrite data are also loaded in batch.

Optimization 5: Replace Manual Relations with Rule-Based Loading

Manual product relations (where a merchandiser manually links products) don't scale. When every product needs 6 related products and you have 10,000 products, that's 60,000 manual links to maintain.

Rule-based relations generate the related products block dynamically based on shared attributes:

$this->_itemCollection = $this->_productFactory->create()
    ->getCollection()
    ->addAttributeToSelect(['name', 'price', 'small_image', 'short_description'])
    ->addAttributeToFilter('attribute_set_id', $this->getProduct()->getAttributeSetId())
    ->addAttributeToFilter('entity_id', ['neq' => $this->getProduct()->getId()])
    ->setPageSize(6)
    ->setCurPage(1)
    ->addStoreFilter()
    ->addPriceData()
    ->addUrlRewrite();

This eliminates the catalog_product_link table entirely and generates relations from shared attributes (same category, same brand, same attribute set). The collection query is a single indexed lookup — no N+1 pattern, no manual data entry.

Use the Sales Rule Approach

For even better results, use sales data to generate relations. Products that were frequently bought together make excellent crosssell recommendations:

SELECT
    soi2.product_id,
    COUNT(*) as frequency
FROM sales_order_item soi1
JOIN sales_order_item soi2 ON soi1.order_id = soi2.order_id
WHERE soi1.product_id = ?
  AND soi2.product_id != soi1.product_id
  AND soi2.product_type = 'simple'
GROUP BY soi2.product_id
ORDER BY frequency DESC
LIMIT 5;

Cache the result per product and regenerate nightly via a cron job. This approach eliminates the link table entirely and shows products that customers actually buy together.

Optimization 6: Disable Unused Relation Types

Not every store uses all three relation types. If you don't use upsells on your storefront, disable the block entirely — don't let the system load products that will never be displayed.

Disable Blocks via Layout XML

<!-- In your theme's layout override: Magento_Catalog/layout/catalog_product_view.xml -->
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <body>
        <!-- Remove upsell block entirely -->
        <referenceBlock name="product.info.upsell" remove="true"/>
        <!-- Or remove crosssell -->
        <referenceBlock name="checkout.cart.crosssell" remove="true"/>
    </body>
</page>

This prevents the block from being created, the collection from being initialized, and the queries from running. It's a free win if the block goes unused.

Optimization 7: Lazy-Load Relations via AJAX

For product pages where relations are nice-to-have but not critical, load them asynchronously after the main page renders. This keeps the initial TTFB and LCP fast.

Create an AJAX Endpoint

// In a custom controller: Vendor\Module\Controller\Product\Related
public function execute()
{
    $productId = (int)$this->getRequest()->getParam('id');
    $product = $this->productRepository->getById($productId);

    $collection = $product->getRelatedProductCollection()
        ->addAttributeToSelect(['name', 'price', 'small_image', 'short_description'])
        ->setPageSize(6)
        ->addStoreFilter()
        ->addPriceData()
        ->addUrlRewrite();

    $data = [];
    foreach ($collection as $related) {
        $data[] = [
            'name' => $related->getName(),
            'price' => $related->getFinalPrice(),
            'image' => $this->imageHelper->init($related, 'product_page_image_small')->getUrl(),
            'url' => $related->getProductUrl(),
        ];
    }

    return $this->jsonResponse(['items' => $data]);
}

Load via JavaScript on the Frontend

define(['jquery'], function($) {
    $(document).ready(function() {
        var productId = $('[data-product-id]').data('product-id');
        $.get('/rest/V1/custom/related/' + productId, function(response) {
            if (response.items.length) {
                var html = response.items.map(function(item) {
                    return '<div class="related-product">' +
                        '<a href="' + item.url + '">' +
                        '<img src="' + item.image + '" alt="' + item.name + '">' +
                        '<span>' + item.name + '</span>' +
                        '<span class="price">€' + item.price + '</span>' +
                        '</a></div>';
                }).join('');
                $('.related-products-container').html(html);
            }
        });
    });
});

This shifts the relation loading off the critical rendering path entirely. The main product page renders fast, and relations load progressively — improving Core Web Vitals while keeping the upsell/crosssell functionality intact.

Optimization 8: Inventory and Price Loading Optimization

Two expensive operations happen for each linked product: stock status check and price calculation. Both involve database queries and computation that multiply across all relations.

Batch Stock Status

Magento's stock status indexer already writes to cataloginventory_stock_status — make sure linked products are reading from the index, not the live stock tables. With MSI installed, ensure the stock status indexer is up to date:

bin/magento indexer:reindex inventory
bin/magento indexer:set-status realtime cataloginventory_stock_status

For relation blocks, use the indexed status directly:

$this->_itemCollection
    ->joinField(
        'is_in_stock',
        'cataloginventory_stock_status',
        'stock_status',
        'product_id=entity_id',
        '{{table}}.stock_id=1 AND {{table}}.website_id=' . $this->_storeManager->getStore()->getWebsiteId(),
        'inner'
    )
    ->addFieldToFilter('is_in_stock', ['eq' => 1]);

This filters out out-of-stock products at the SQL level, avoiding loading products that would only be hidden later.

Cache Price Calculations

Price calculation for linked products involves catalog price rules, tier prices, and tax. Cache the final price per product per customer group:

// Use the price index table directly instead of calculating on-the-fly
$this->_itemCollection
    ->joinField(
        'final_price',
        'catalog_product_index_price',
        'final_price',
        'entity_id=entity_id',
        '{{table}}.customer_group_id=0 AND {{table}}.website_id=' . $websiteId,
        'inner'
    );

Reading from the price index table is a single join — far cheaper than running the full price calculation pipeline for each product.

Checklist: Product Relations Performance Audit

Run through this checklist to audit your store:

1. Count relations per product — any product with more than 8 relations of one type needs cleanup
2. Profile a PDP with 10+ relations — measure query count and total time
3. Limit addAttributeToSelect to only needed attributes (name, price, image, url_key, short_description)
4. Set setPageSize() on relation collections to limit at SQL level
5. Remove unused relation blocks via layout XML overrides
6. Add block-level caching with proper cache key and invalidation
7. Consider AJAX lazy-loading for non-critical relations
8. Use indexed price and stock data instead of live calculations
9. Consider rule-based relations to eliminate manual link maintenance
10. Reindex regularly — stale price and stock indexes force fallback to live calculation

Conclusion

Product relations are one of those features that feels free — you just link some products in the admin and they appear on the storefront. But the underlying query pattern is fundamentally inefficient: for each relation, Magento loads a full product entity, calculates pricing, checks stock, and renders a block. Multiply that by 10–20 products per page, and you've added hundreds of milliseconds to every product detail page.

The optimizations in this guide range from quick wins (limiting page size, reducing attribute selection) to architectural changes (batch loading, AJAX deferred loading, rule-based relations). Start with the quick wins on your worst-performing product pages, measure the improvement, and work toward the architectural changes as your catalog grows.

Remember: a product page that loads in 300ms with 6 well-chosen related products will convert better than a page that loads in 1.2s with 20 related products that the customer scrolls past anyway. Fewer, faster, better.

Comments

[Hayrullah Kar]

Solid rundown, but optimization 3 won't fire as written. AbstractBlock sha1's
the whole getCacheKeyInfo() array into BLOCK_, so
remove('PRODUCT_RELATIONS_'.$id.'_') never matches a real cache id, and there's
no prefix-delete in Magento's cache anyway. getCacheTags() with the product tag
plus a clean by tag is what actually invalidates it. I'd also put customer group
in that key, since the block renders final and tier prices, otherwise group A
gets served group B's price.