Magento Indexers: Known Issues and Fixes

Magento (Adobe Commerce) indexes product data to improve the performance of a website’s storefront. Using indexers, Magento collects and transforms data into special tables for easy access and processing. However, issues arise when indexing is not properly executed.

In this article, we’ll look at three known indexing issues, their causes, and resolutions.

Products disappearing from categories during reindex

This issue occurs in older Magneto 2 versions and for multiple reasons:

  1. When indexers don’t use a replica table to write information before updating it in the main table.
  2. When the older indexers remove data from the indexer table before writing new data, the process is not split into batches, and it takes too long to get new data in.
  3. When shared indexer processes (such as category-product and product-category) affect the same table.

These issues have been resolved in the series of MDVA-30977 patches— addressing shared indexes, adding batching to configurable inventory and price indexers, and adding more replica usages.

Catalogrule reindex taking too long

This usually happens because of poorly set up rule conditions. Magento’s implementation results in a UNION call on the collection load for each individual condition added; scheduled reindex of catalog rules taking too long is a sign of rule misconfiguration.

For example, instead of using a rule such as SKU is IN ..., ..., ..., a set of individual rules such as SKU is X, SKU is Y, SKU is Z, ... is used.

This is a fairly common case, but reindex performance decreases in proportion to the amount of individual conditions on the price rule.

For example, we have seen that having too many conditions on price rules can cause an incremental scheduled reindex to take over half an hour, or many hours for a full reindex.

And such a slowdown on one of the indexers can delay or crash reindexing for the rest, as they are all on one cron group.

Configurable product price reindex taking too long

This can happen when there are too many stores and too many variants on the products.

One of the known causes is a poorly formatted WHERE condition in the Configurable Price indexer, which negatively affects query performance when out-of-stock product display is turned on.

Magento\\ConfigurableProduct\\Model\\ResourceModel\\Product\\Indexer\\Price\\Configurable::fillTemporaryOptionsTable

        if ($this->isConfigShowOutOfStock()) {
            $select->join(
                ['si' => $this->getTable('cataloginventory_stock_item')],
                'si.product_id = l.product_id',
                []
            );
            $select->where('si.is_in_stock = ?', Stock::STOCK_IN_STOCK);
        }
        // ...
        if ($entityIds !== null) {
            $select->where('le.entity_id IN (?)', $entityIds);
        }

The conditions above result in the SQL query with a filters like this:

WHERE (si.is_in_stock = 1) OR (si_parent.is_in_stock = 0) AND (le.entity_id IN (...)

This is not grouped correctly, and can be rewritten to have a query in the format of:

WHERE ((si.is_in_stock = 1) OR (si_parent.is_in_stock = 0)) AND (le.entity_id IN (...))

These issues can be considered as fairly common and their solutions are uncomplicated, yet many developers fail to address them for the simple reason that they don’t know where the problem is coming from.

Are you experiencing other indexing issues with your Magento eCommerce store? Our team of certified Magento Developers can help. Scandiweb is the most certified Magento team in the world with 200+ Magento-certified developers and solution specialists. Tell us about your issue here.

If you enjoyed this post, you may also like