Magento – magento 2 main.WARNING: Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size

Change the setting in php.ini and restart server. There is no need to change in code or put in start of file because best solution is to do in php.ini.

You can also try in .htaccess.

Comment if face issue again. I will help you to sort out.

Magento can increase the memory for processing a large amount of data by using memory engines instead of InnoDB. The algorithm increases the memory value for the max_heap_table_size and tmp_table_size MySQL parameters.

When the allocated memory size for a temporary table will be greater than 20% of innodb_buffer_pool_size, the error message is written to the Magento log.

To prevent this error message, you need to update default Batching configuration of catalog_category_product (Category Products) indexer because "Current batch size: 100000".

For more details, take a look here.

Try following code:

app/code/[VendorName]/[ModuleName]/etc/adminhtml/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\Catalog\Model\Indexer\Category\Product\Action\Full">
        <arguments>
            <argument name="batchRowsCount" xsi:type="array">
                <item name="configurable" xsi:type="number">100000</item>
            </argument>
        </arguments>
    </type>
</config>

Note: Decrease the batchRowsCount to resolve the error.