How to Optimize Magento 2 Reindex?

Since Magento architecture is structured in a way to store data in different database tables, all data have to be reindexed when you make any changes to your store. And while you can reindex Magento via CLI or even the admin panel, this process is still time-consuming. 

In this guide, we want to help you optimize the process.

Since the Catalog Category Product indexer takes the longest to execute we'll take it as an example for this article. So, you can optimize Magento reindex execution time by changing with batch_size of the Catalog Category Product indexer.

However, to get a better picture of how this works, let's define what is a batch size and why you need to modify it. 

How Magento Reindex Works?

Batch size is a number of indexes that will be processed at a time, one MySQL query.

Imagine that you have a store with 40k products, and you do some changes, like updating prices in bulk, to all of your products. This will cause 40K indexes to be invalidated. 

Magento splits data for reindexing in the following way:

Number of batches = number of indexes / batch size

However, batching is available not for all indexers. Check the available indexers and their default batch size in this table:

Let's consider the following example and calculate batch size for catalog_product_price indexer.

• Default batch size for catalog_product_price - 5000
• Count of products - 40000

As a result:

Number of batches = 40000/5000 = 8 batches

If you run reindex, Magento will process the 1st of 8 batches then the 2nd and so on. So, batches will be processed one by one and some other queries like product saving may have a chance to be executed.

Now, let's calculate batch size for catalog_category_price indexer:

• Default batch size for catalog_category_product - 100000
• Count of products - 40000

As a result:

Number of batches = 40000/100000 = 0.4 = 1 batch

So, we have one batch. This means that all 40k indexes will be processed at once which overloads your DB and causes the deadlocks issues.

How to Optimize Magento 2 Reindex?

Considering the examples we've just provided, it is better to reduce 'batch_size' for 'catalog_category_product' and use values up to 5000. 

For that, add the following lines to your app/etc/env.php file:

'indexer' => [

        'batch_size' => [

            'catalog_category_product' => 1000
        ]
 ],

To optimize other indexers, you can follow Magento documentation.