Category Products index taking too much time?
bin/magento index:reset
bin/magento index:reindex
Stuck at category products index …
Here is the solution if you are using MariaDB 10.4
You need to update options/parameters for your db. If using RDS, edit “DB instance parameter group” that you are using and update these 2 values:
optimizer_switch='rowid_filter=off'
optimizer_use_condition_selectivity = 1
After these changes, restart your database. In aws, RDS automatically updates these parameters without any downtime or requirement for restarting. You will see noticeable difference in indexer time.
Reference:
https://devdocs.magento.com/guides/v2.4/performance-best-practices/configuration.html
https://devdocs.magento.com/guides/v2.4/extension-dev-guide/indexer-batch.html