Magento 2 – Category Products index taking 10mins

Hi,

I am on 2.4.6 and baffled with a certain behaviour of this indexer.

After uploading a batch of 1000 new products into the store, I was terrified to notice the Category Products index process jumping from 4 seconds to 10 minutes.

To troubleshoot, I split the file into smaller batches based on category. So 1 upload may have had 100 products and another file may only have 1.

I was able to determine that even 1 product can trigger a jump of as much as 35seconds, i.e. going from 4seconds to reindex this indexer to 40. the worst batch took reindexing to 4mins 30secs.

Deleting the respective products immediately after takes the indexer time back down to 4secs.

I have looked at the custom attributes of these products and I cannot see a pattern that is any different to the products that do not impact the indexer negatively. Attributes are shared between these sets of products and they may or may have Show In Listing turned on, it makes no difference.

Anyone has experienced anything similar?

thanks

​