I have multiple active shopping cart rules on my website, including some older rules. The problem is that Magento validates these rules every time there is an update to the shopping cart or when a customer proceeds to checkout.
The method setValidationFilter() calls getNoCouponCodeSelect() from the class vendor/magento/module-sales-rule/Model/ResourceModel/Rule/Collection.php, which is responsible for this validation. It goes through all the active rule_id values to check if there is a compatible rule based on the current customer’s characteristics.
The SQL query that is impacting the performance in the database is as follows:
SELECT main_table.*,
NULL AS code
FROM salesrule AS main_table
INNER JOIN
(SELECT DISTINCT salesrule_website.row_id
FROM salesrule_website
WHERE (website_id IN (?))) AS website ON main_table.row_id = website.row_id
INNER JOIN salesrule_customer_group AS customer_group_ids ON
main_table.row_id = customer_group_ids.row_id
AND customer_group_ids.customer_group_id = ?
WHERE ((is_active = ‘?’)
AND (main_table.coupon_type = ?)
AND (main_table.rule_id IN (8100168,
8100187,
8100188,
8100189,
8100195,
8100196,
8100209,
8100210,
8100213,
8100219,
8100225,
….
8100637))
AND (is_active = ‘?’))AND (main_table.created_in <= ‘?’)
AND (main_table.updated_in > ‘?’)
ORDER BY sort_order ASC
How can I improve the performance of this query while maintaining the same business rules?
Note: I would prefer not to disable the shopping cart rules.