ACSD-50817: Optimizes cron job sales_clean_quotes to run faster

The ACSD-50817 patch optimizes the cron job sales_clean_quotes to run faster by adding a composite index on the store_id and updated_at columns in the quote table. This patch is available when the Quality Patches Tool (QPT) 1.1.31 is installed. The patch ID is ACSD-50817.

Affected products and versions

The patch is created for Adobe Commerce version:

  • Adobe Commerce (all deployment methods) 2.4.5-p1

Compatible with Adobe Commerce versions:

  • Adobe Commerce (all deployment methods) 2.3.7 - 2.4.6
NOTE
The patch might become applicable to other versions with new Quality Patches Tool releases. To check if the patch is compatible with your Adobe Commerce version, update the magento/quality-patches package to the latest version and check the compatibility on the Quality Patches Tool: Search for patches page. Use the patch ID as a search keyword to locate the patch.

Issue

The cron job sales_clean_quotes is too slow. With this patch, it has been optimized to run faster by adding a composite index on the store_id and updated_at columns in the quote table.

Steps to reproduce:

  1. Generate 50-80M of quotes with updated_at set as < 30 days period.

  2. Run the cron job sales_clean_quotes or the following query on the quote table:

    code language-cron
    SELECT COUNT(*) FROM `quote` AS `main_table` WHERE (`store_id` = '1') AND (`updated_at` <= '2023-02-25') AND (`is_persistent` = '0')
    
    SELECT * FROM `quote` AS `main_table` WHERE (`store_id` = '1') AND (`updated_at` <= '2023-02-25') AND (`is_persistent` = '0') LIMIT 50
    

Expected results

Cron job sales_clean_quotes is optimized to run faster by adding a composite index on the store_id and updated_at columns in the quote table.

Actual results

The query is too slow.

Apply the patch

To apply individual patches, use the following links depending on your deployment method:

To learn more about Quality Patches Tool, refer to:

For info about other patches available in QPT, refer to Quality Patches Tool: Search for patches in the Quality Patches Tool guide.

recommendation-more-help
8bd06ef0-b3d5-4137-b74e-d7b00485808a