This article provides a fix for poor performance due to full reindexing (where data in the indexing-related database tables is updating).
Constant flushing and index rebuilding are some of the reasons for performance degradation. Additionally, constant full reindexing adds locks on tables making the website work much slower than expected.
Actions that can produce full reindexing were performed from admin including:
These actions should be run outside of business hours to make sure these actions do not affect performance during business hours.
Third-party extensions can also cause full reindex. Full reindex may also be manually run from CLI. To find out if you have indexes being reindexed and potentially causing performance downgrade:
Perform this query to find the indexers that were fully reindexed in the last 15 minutes:
SELECT * FROM indexer_state WHERE updated > NOW() - INTERVAL 15 MINUTE;
An indexer name in the output means that the indexer has been reindexed at least once during the last 15 minutes.
If you found frequent full reindexation, investigate the following:
Run reindexing only when necessary. For steps, review Configure Indexers in our developer documentation. A general recommendation and best practice is to allow the partial reindexation mechanism to take care of data reindexation with no manual action required from a merchant. All reindexation should be done using native Adobe Commerce functionality (Mview). Mview performs partial reindexation, which is the most efficient way to reindex data. To learn about Mview, refer to Indexing overview: Mview in our developer documentation.