Store Speed issue with Reindex. Magento 2

Question

I have a Magento2.3.5 Version but that store is causing a strange issue Store is really fast when just Reindex is runned but after 2 or maybe 3 hrs it became really slow and I have to run Reindex again. So to Trace that issue I've checked System.log file which was showing
[2024-06-28 11:12:46] main.CRITICAL: Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "881" already exists. [] []
so for that I've created a plugin which code is

di.xml

<?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\Eav\Model\Entity\Collection\AbstractCollection">
    <plugin name="find_duplicate_entry" type="Treezee\DuplicateEntry\plugin\Collection" sortOrder="20"/>
 </type>
</config>

while the plugin code is

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 */
namespace Treezee\DuplicateEntry\plugin;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Magento\Framework\Option\ArrayInterface;
 
class Collection
{
    /**
     * @param \Magento\Eav\Model\Entity\Collection\AbstractCollection $subject
     * @param \Closure $process
     * @param \Magento\Framework\DataObject $dataObject
     * @return $this
     */
    public function aroundAddItem(\Magento\Eav\Model\Entity\Collection\AbstractCollection $subject, \Closure $process, \Magento\Framework\DataObject $dataObject)
    {
        try{
            return $process($dataObject);
        }catch ( \Exception $e){
            return $this;
        }
    }
}

now this code fixes the above issue but still the Store Reindex issue was there so now when I looked at the System.log file the it shows Now I dont know why this issue is happening or it is related to speed issue or not and how to fix that.. any help would be really appreciated Thanks.

Answer 1

Please Drop the catalog_category_product_index and catalog_category_product_index_tmp table and Be sure to take the backup before deleting. and then you can use the partial index on your site.

Magento provide two ways :

• Update on Save: The data is updated when changes are saved.
• Update by Schedule: The changed data is updated according to the schedule.

You can simply set the index on update on save instead on running all indexes.

Then it will helps solve your problem