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 (?))
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.
