Published: April 18, 2017

In a recent New Relic performance audit on a client’s site, we found that the Mage_SalesRule totals collectors were causing some serious slowness. At best, totals collection would execute in around 1.2s when coupons were at play. However, in the worst cases, it would take up to 30 seconds.

Checking the database I found that the salesrule_coupon table had over 20 million records in it. In this post I’ll show you how we were able to cut that down to literally milliseconds and keep totals collection performant, even with 20 million coupons in the database.

The Query

From New Relic, we were able to see that the following query was slow…

The Solution

Per my blog post on catalog search, a more efficient way to achieve the desired goal is via a UNION. Essentially, the idea is to prepare two SELECT statements, one for both conditions on either side of the OR and UNION them together. This allows MySQL to efficiently use indexes for each of the SELECTs.

The Implementation

In order to do this in Magento, the best option is to rewrite the setValidationFilter method in the Mage_SalesRule_Model_Resource_Rule_Collection class. Here is the full rewritten class.

What About Magento 2?

While I solved this issue for a site running Magento 1, looking at Magento\Quote\Model\Quote\Address\Collection::setValidationFilter() in the latest M2 codebase (v2.1.5) I can see this is still an issue.

Gotchas

Keep in mind that this change causes Mage_SalesRule_Model_Resource_Rule_Collection’s internal SELECT to differ from the SELECT generated by core code. When I first wrote this code I found that we ran into issues with Bronto code which made assumptions about what Mage_SalesRule_Model_Resource_Rule_Collection would return after calling setValidationFilter.