[Issue] Remove unnecessary clause from price index queries

[m2-assistant]

This issue is automatically created based on existing pull request: #32223: Remove unnecessary clause from price index queries

---

Description (*)

This removes a line that was rendered unnecesary by #22575. Currently there are two filters in the where clause for entity ID's. Since the second one explicitly lists the individual entity ID's, the BETWEEN AND clause is redundant. In testing on mariadb 10.2 and 10.3, removing the BETWEEN AND clause yields significant improvements in catalog price indexer performance on stores with large catalogs.

Related Pull Requests

Fixed Issues (if relevant)

Manual testing scenarios (*)

1. Run catalog_product_price reindex on store with a large catalog of products.
2. Remove or comment out the line that this pull request removes.
3. Run reindex again and compare speed to first run.

Questions or comments

Contribution checklist (*)

• Pull request has a meaningful description of its purpose
• All commits are accompanied by meaningful commit messages
• All new or changed code is covered with unit/integration tests (if applicable)
• All automated tests passed successfully (all builds are green)

[engcom-November]

Verified the behavior on Magento 2.4-develop instance with 10k products and Product price reindex took 01 sec but where as Catalog Search index took 18 seconds to reindex.

[m2-assistant]

Hi @engcom-November. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Add label Issue: Confirmed once verification is complete.

• 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-November]

Verified the behavior again on Magento 2.4-develop instance with 19k products (With both simple and configurable products combined) and Product Price index took 00:06:25 time during reindex.

[engcom-November]

Verified the issue again on Magento 2.4-develop instance with and without PR changes. On an instance with 20k products - Product Price index took 00:06:25 time during reindex. After removing / commenting the line mentioned in PR , Product Price index took 05 seconds during reindex on an instance with 40,000 products. Hence confirming this issue.

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.adobe.com/browse/AC-6996 is successfully created for this GitHub issue.

[m2-assistant]

✅ Confirmed by @engcom-November. Thank you for verifying the issue.
Issue Available: @engcom-November, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.