Scaling Magento's SalesRule Module to Handle 20 Million Coupons and Beyond

Published: April 18, 2017

Tags:

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.

A screenshot showing New Relic's production traces

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…

SELECT `main_table`.*, `rule_coupons`.`code`
FROM `salesrule` AS `main_table`
  INNER JOIN `salesrule_customer_group` AS `customer_group_ids`
    ON main_table.rule_id = customer_group_ids.rule_id
    AND customer_group_ids.customer_group_id = ?
  LEFT JOIN `salesrule_coupon` AS `rule_coupons`
    ON main_table.rule_id = rule_coupons.rule_id
    AND main_table.coupon_type != ?
WHERE (EXISTS (SELECT ? FROM `salesrule_website` AS `website` WHERE (website.website_id IN (?)) AND (main_table.rule_id = website.rule_id)))
  AND (from_date IS null OR from_date <= ?)
  AND (to_date IS null OR to_date >= ?)
  AND (`is_active` = ?)
  AND (main_table.coupon_type = ? OR ((
    (main_table.coupon_type = ? AND rule_coupons.type = ?)
      OR (main_table.coupon_type = ? AND main_table.use_auto_generation = ? AND rule_coupons.type = ?)
      OR (main_table.coupon_type = ? AND main_table.use_auto_generation = ? AND rule_coupons.type = ?)
    ) AND rule_coupons.code = ?))
  AND (`main_table`.`coupon_type` IN(?, ?))
ORDER BY sort_order ASC

Tracing it back through the code we found that this query is largely a result of the code in the setValidationFilter method of Mage_SalesRule_Model_Resource_Rule_Collection.

Essentially, this query will fetch a list of sales rule candidates for the current quote, which will later be processed to determine the best fit, if there is one.

The Issue

The issue with this query is here…

WHERE main_table.coupon_type = ? OR ((
  (main_table.coupon_type = ? AND rule_coupons.type = ?)
    OR (main_table.coupon_type = ? AND main_table.use_auto_generation = ? AND rule_coupons.type = ?)
    OR (main_table.coupon_type = ? AND main_table.use_auto_generation = ? AND rule_coupons.type = ?)
) AND rule_coupons.code = ?)

Essentially, MySQL cannot leverage the indexes due to the WHERE OR. I blogged about a similar issue with scaling catalogsearch_query. This causes it to do a full table scan of the salesrule_coupon table, which, as mentioned, had 20 million rows.

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.

<?php

class SomethingDigital_SalesRulePerf_Model_Resource_Rule_Collection
    extends Mage_SalesRule_Model_Resource_Rule_Collection
{

    /**
     * Filter collection by specified website, customer group, coupon code, date.
     * Filter collection to use only active rules.
     * Involved sorting by sort_order column.
     *
     * Overwrite the parent for better performance.
     *
     * We UNION where noCoupon and yesCoupon SELECTs rather than use an OR
     *
     * @param int $websiteId
     * @param int $customerGroupId
     * @param string $couponCode
     * @param string|null $now
     * @use $this->addWebsiteGroupDateFilter()
     *
     * @return Mage_SalesRule_Model_Resource_Rule_Collection
     */
    public function setValidationFilter($websiteId, $customerGroupId, $couponCode = '', $now = null)
    {
        if (!$this->getFlag('validation_filter')) {
            parent::setValidationFilter($websiteId, $customerGroupId, $couponCode, $now);

            if (!strlen($couponCode)) {
                return $this;
            }

            $connection = $this->getConnection();

            $noCoupon = $this->baseSelectForUnion();
            $noCoupon->where($connection->quoteInto(
                'main_table.coupon_type = ? ',
                Mage_SalesRule_Model_Rule::COUPON_TYPE_NO_COUPON
            ));

            $yesCoupon = $this->baseSelectForUnion();
            $yesCoupon->where($this->yesCouponWhere(), $couponCode);

            $subselect = $connection->select()->union(array(
                $noCoupon,
                $yesCoupon,
            ));

            $this->getSelect()->reset();
            $this->getSelect()->from(array('main_table' => $subselect));
        }

        return $this;
    }

    /**
     * Get a select to use for the UNION
     *
     * Discards the last applied WHERE condition (they will be applied
     * separately and UNION-ed)
     *
     * @return Varien_Db_Select
     */
    protected function baseSelectForUnion()
    {
        $select = clone $this->getSelect();
        $where = $select->getPart('where');
        array_pop($where);
        $select->setPart('where', $where);

        return $select;
    }

    /**
     * Get the WHERE for the yes coupon SELECT.
     *
     * This logic can be found in setValidationFilter() in the parent.
     *
     * @return string
     */
    protected function yesCouponWhere()
    {
        $connection = $this->getConnection();

        $orWhereConditions = array(
            $connection->quoteInto(
                '(main_table.coupon_type = ? AND rule_coupons.type = 0)',
                Mage_SalesRule_Model_Rule::COUPON_TYPE_AUTO
            ),
            $connection->quoteInto(
                '(main_table.coupon_type = ? AND main_table.use_auto_generation = 1 AND rule_coupons.type = 1)',
                Mage_SalesRule_Model_Rule::COUPON_TYPE_SPECIFIC
            ),
            $connection->quoteInto(
                '(main_table.coupon_type = ? AND main_table.use_auto_generation = 0 AND rule_coupons.type = 0)',
                Mage_SalesRule_Model_Rule::COUPON_TYPE_SPECIFIC
            ),
        );
        $orWhereCondition = implode(' OR ', $orWhereConditions);

        return '(' . $orWhereCondition . ') AND rule_coupons.code = ?';
    }
}

The Improved SELECT

Now, our refactored Mage_SalesRule_Model_Resource_Rule_Collection class will generate the following SELECT

SELECT `main_table`.* FROM (
  SELECT `main_table`.*, `rule_coupons`.`code`
  FROM `salesrule` AS `main_table`
    INNER JOIN `salesrule_customer_group` AS `customer_group_ids`
      ON main_table.rule_id = customer_group_ids.rule_id
      AND customer_group_ids.customer_group_id = 1
    LEFT JOIN `salesrule_coupon` AS `rule_coupons`
      ON main_table.rule_id = rule_coupons.rule_id
      AND main_table.coupon_type != 1
    WHERE (EXISTS (SELECT 1 FROM `salesrule_website` AS `website` WHERE (website.website_id IN (1)) AND (main_table.rule_id = website.rule_id)))
    AND (from_date is null or from_date <= '2017-01-14')
    AND (to_date is null or to_date >= '2017-01-14')
    AND (`is_active` = '1')
    AND (main_table.coupon_type = 1 )

  UNION

  SELECT `main_table`.*, `rule_coupons`.`code`
  FROM `salesrule` AS `main_table`
    INNER JOIN `salesrule_customer_group` AS `customer_group_ids`
      ON main_table.rule_id = customer_group_ids.rule_id
      AND customer_group_ids.customer_group_id = 1
    LEFT JOIN `salesrule_coupon` AS `rule_coupons`
      ON main_table.rule_id = rule_coupons.rule_id
      AND main_table.coupon_type != 1
  WHERE (EXISTS (SELECT 1 FROM `salesrule_website` AS `website` WHERE (website.website_id IN (1)) AND (main_table.rule_id = website.rule_id)))
  AND (from_date is null or from_date <= '2017-01-14')
  AND (to_date is null or to_date >= '2017-01-14')
  AND (`is_active` = '1')
  AND (((main_table.coupon_type = 3 AND rule_coupons.type = 0)
    OR (main_table.coupon_type = 2 AND main_table.use_auto_generation = 1 AND rule_coupons.type = 1)
    OR (main_table.coupon_type = 2 AND main_table.use_auto_generation = 0 AND rule_coupons.type = 0))
  AND rule_coupons.code = 'XXXXXXXXX')
) AS `main_table` ORDER BY sort_order ASC

This query executes in just a few milliseconds.

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.

$rules = Mage::getModel('salesrule/rule')
    ->getCollection()
    ->setValidationFilter($websiteId, $customerGroupId, $couponCode)
    ->addFieldToFilter('main_table.coupon_type', array('in' => array(Mage_SalesRule_Model_Rule::COUPON_TYPE_SPECIFIC, Mage_SalesRule_Model_Rule::COUPON_TYPE_AUTO)));

The subselect with the main_table alias was added to workaround this.

Conclusion

I hope that some of you found this post helpful. If you have any questions or comments, feel free to drop a note below, or, as always, you can reach me on Twitter as well.

Max ChadwickHi, I'm Max!

I'm a software developer who mainly works in PHP, but also dabbles in Ruby and Go. Technical topics that interest me are monitoring, security and performance.

During the day I solve challenging technical problems at Something Digital where I mainly work with the Magento platform. I also blog about tech, work on open source and hunt for bugs.

If you'd like to get in touch with me the best way is on Twitter.