Tag: speed

---

Speed Up Customer Segments

An issue you may encounter on larger sites is massive DB load spikes when there is a high order volume and large number of customers stored in the DB. The problem stems from the Enterprise Edition’s Customer Segments module and the way it checks to add a new customer into a segment.

The main culprit is the Enterprise_CustomerSegment_Model_Segment_Condition_Combine_Root class, specifically the _prepareConditionsSql method. This is what generates the SQL that will be run to verify if a customer should be added to this segment when they place an order or sign up. It generates a query like:

SELECT 1 FROM `customer_entity` AS `root` WHERE 
((
    IF((
        SELECT 1 FROM `sales_flat_order_item` AS `item`
        INNER JOIN `sales_flat_order` AS `sales_order` ON item.order_id = sales_order.entity_id
        INNER JOIN `core_store` AS `store` ON sales_order.store_id=store.store_id
        WHERE 
        (sales_order.customer_id = '11111111') 
        AND (store.website_id='1') 
        AND (EXISTS (SELECT `main`.`entity_id` FROM `catalog_product_entity` AS `main` WHERE (main.sku = 'ITEM1') 
        AND (main.entity_id = item.product_id))) LIMIT 1
    ), 1, 0) = 1
))

which has a major issue, the main outer query has no limit! Even worse, it has no where condition to limit the number of rows being returned so what you get is a HUGE amount of rows containing “1” whenever the inner customer+item match is true. When you have 1 million or more customers, this can lead to the DB creating temporary tables on disk since it no longer fits in the maximum memory temp table size which leads to load spikes if you are getting any sizable amount of orders being placed. Continue reading →

---

Adding Customer Group Access Type To Turpentine+Varnish

Update: It looks like the pull request has been accepted and this adjustment has been fully merged into the base Turpentine extension!

I ran into an issue with one of the websites I work on while trying to add Varnish caching in front of their store. They used a lot a catalog price rules which meant that the price on the products would change based on the customer group for the customer viewing the page. At this point I had 2 options, setup Varnish to private cache all the products (which defeats the purpose of Varnish since it would rarely serve a cached page more than once) or figure out how to have Varnish save a page based on the customer group and serve to others with the same group. Continue reading →

---