Magento’s indexer process is complex, and inefficiencies have made their way into the main branch. Although these have been patched, not everyone has quickly updated to the latest version. Recently, I investigated some issues in Magento 2.4.6-p6 and found that with a few patches applied, indexer performance improved 10x.
This improvement may not apply to every store, but if you have multiple store views and complex product types, you’re likely to benefit from these patches.
I observed the improvements after applying three patches—two of which were Magento Quality Patches:
ACSD-53347
&
ACSD-53583
Follow the instructions on the Magento Quality Patches Tool page to install these. They can be installed via the CLI tool or by directly using the .patch files in the GitHub repository: https://github.com/magento/quality-patches/blob/fcff8bb4a8d2dbee39bac99e925dd129a24809d6/patches/os/ACSD-53347_2.4.6.patch
The second patch that I found most impactful was this, which has yet to be raised as a Quality tool patch.
https://github.com/magento/magento2/issues/37367
They've produce a standalone patch here, here https://github.com/magento/magento2/files/11221155/indexation_issue.patch.txt
With these patches in place I saw a 10x increase in indexer performance. They can be applied with the cweagans/composer-patches tool to ensure they'd be applied to all environments automatically.
Make sure you test this on a staging environment before deploying live.