I have a Magento 2.2.5 store with ~20k products and ~250 categories arranged in a tree of maximum 2 sub categories.

One of the top categories has 5k products and when I try to remove a product from the category, or remove the category for a specific product the server takes more than 2 minutes and times out.

After reading the general_log, I saw thousands of queries like this:

SELECT `e`.* FROM `tmp_select_e5e424ef98e4a8a4026e18df9d8088e0` AS `e` WHERE (hash_key = '1_44083')

peaking the database server to 100%: enter image description here

I think this has something to do with url_rewrites, but I can't seem to understand how is this a problem with only 5k products.

Are there any configurations I can set maybe loosing functionalities, to be able to do something as simple as removing a product from a category?

Thanks a lot.

1 Answer 1


Magento2 create temp table/ using those temp table during indexing.You cannot avoid the temp concept. One thing, you can do in this case that changes Batch, Process count.

As you have 5k products assign for a particular category the might be the cause of this CPU usage during Category Product Indexing.

For this issue, you might need to change batchRowsCount for Magento\Catalog\Model\Indexer\Category\Product\Action\Full




Create di.xml at your module Your_Module_Name}/etc/di.xml. and change the batchRowsCount parameter value to 10000 OR 5000.

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
  <type name="Magento\Catalog\Model\Indexer\Category\Product\Action\Full">
      <argument name="batchRowsCount" xsi:type="number">5000</argument>
  • Hi, thanks for the answer. I tried this but nothing changed. The default value for batchRowsCount is 100.000, so dropping it to 10.000 seems like the opposite direction or not? I also tried with 200.000 and same problem.
    – amcastror
    Jul 24, 2019 at 14:25

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.