Description
Preconditions (*)
- Magento 2.3.5
- PHP 7.4
Steps to reproduce (*)
- Add a product to the cart without using a coupon.
- Create multiple cart price rules and coupons.
Expected result (*)
- High performance because finding cart rules is theoretically a simple task.
Actual result (*)
- Incredibly low performance, as the SQL query is poorly constructed, because even without adding a specific coupon, it filters all registered ids (coupons and cart price rules).
Description
We currently have around 7,000 sales rules (most of them inactive or expired) and around 2,000 coupons. The query, which is currently being used by the sales rules module, is extremely inefficient in the database and therefore impacts the performance of the shopping cart.
The query that is generated by the _getRules method in vendor/magento/module-sales-rule/Model/Validator.php currently looks like this:
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
So every time there is an update to the cart and the customer is not using a coupon, magento hits that query. And filter all cart rules and coupons through the snippet: AND (main_table.rule_id IN (?))
I realized that by changing the filter_text attribute in the magento_salesrule_filter table to 'false', the id's do not pass through this query. The question is, why is the filter_text attribute always 'true', even for disabled rules?