I am working on Magento 2.3.2. When I run re-index command php bin/magento i:rei Design Config Grid and Customer Config Grid is completed. But it is taking too much time to re-index Category Products and Product Categories

I checked the SHOW PROCESSLIT; in my database and I am getting the following query which is running from a while.

INSERT INTO `catalog_category_product_index_store1

After a long time, it is displaying

INSERT INTO `catalog_category_product_index_store2

I tried to reset the indexer and again re-index only those two indexers. But the same thing happening.

I have 60k Products in my Magento Store.

It has suddenly happened from a few days in live only. In local, it is re-indexing everything. The issue is only in live. I have the same number of products in local.

Is it any permission issue to any folder like var or generated?

Please guide me on that. Thanks in advance.

5 Answers 5


Most probably the difference is that on live you have way more traffic than on your local env and this is interfering with the queries.
I recommend reducing the number of rows processed at once by the indexer.
You can do that by adding this in the di.xml from one of your custom modules.

<!-- this is for the category product indexer -->
<type name="Magento\Catalog\Model\Indexer\Category\Product\Action\Full">
        <argument name="batchRowsCount" xsi:type="number">500</argument>
<!-- this is for the eav indexer -->
<type name="Magento\Catalog\Model\ResourceModel\Product\Indexer\Eav\BatchSizeCalculator">
        <argument name="batchSizes" xsi:type="array">
            <item name="decimal" xsi:type="number">100</item>
            <item name="source" xsi:type="number">100</item>

I have no procedure to determine the numbers (500 and 100) in the example above. I just used "rule of thumb" for them in the project I'm currently on that has a catalog size of around 50k products, around 170 attributes and around 20 categories.

More information about indexer optimization: https://devdocs.magento.com/guides/v2.3/extension-dev-guide/indexer-batch.html

  • Hello @Marius I have "INSERT INTO search_tmp_5e4a1edfb9ca89_07951907" query that is taking too much time while reindex manually or by cron. Sometimes there are more then 15-20 query and the CPU usage get 1500% so above solution will resolve this issue too ? Commented Feb 17, 2020 at 5:11
  • I don't think that query should get executed during indexing. Anyway, maybe you should check this: github.com/magento/magento2/issues/15545
    – Marius
    Commented Feb 17, 2020 at 5:55
  • I implement as per your solution. I put 4000 number in "Magento\Catalog\Model\Indexer\Category\Product\Action\Full". I run the re-indexer for only catalog_category_product. It get completed in 2:45 min. But now I run all indexer. and it get stuck on same catalog_category_product. I checked the mysql processlist and there are too many query which was not before INSERT INTO search_tmp_5e4a291c782d95_52127920. There are 30+ query and the site is down. Commented Feb 17, 2020 at 6:02

In addition to @Marius♦ answer, Please optimized Category & product relation means do not unnecessarily assign products to the category.

Suppose, If you will feel a product does not need to be assigned to a category and then you should remove that product that category.That why catalog_category_product_index and catalog_category_product table.

It will also help to indexing optimization.

That wrong assignment between product & category is a common mistake the people do.


if you use Maria db 10.4 and face long time when reindexing you need to modify default MariaDB configuration and set the following parameters:


optimizer_use_condition_selectivity = 1

Ref: https://experienceleague.adobe.com/docs/commerce-operations/performance-best-practices/configuration.html


@david-coder - I am just thinking the only difference between local & prod may be cron settings. You can verify the cron too which may try to run some process and may be delayed. It may impact the reindex process.


I have this issue many times in my cases this issue can resolved by running

php bin/magento indexer:reset

then run

php bin/magento indexer:reindex

And wait it will take time do not exist from command and do not run any other command.Let it take as much as time just wait. It will take time because you have lots of products so wait. Your indexer Process will get completed. Hope this will work for you.Mark answer accepted if this solved your problem Thank you :)

  • 1
    Thanks but I already tried by reset and again reindex. Commented Feb 13, 2020 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.