Author: Marshall Thornton
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
Setting Up XDebug With PHPStorm
This is a little off topic from Magento, but I have found it to be extremely useful setting up XDebug with PHPStorm when developing anything with Magento. You can probably use most of these steps with any other IDE and just replace the PHPStorm specific steps with your IDE ones.
The first and most obvious step, install and setup XDebug in your development environment. I prefer to use a VirtualBox VM running CentOS for development so for that I can just run
yum install php-pecl-xdebug
or
pecl install xdebug
to get the extension added into the system. You will then need to make sure it is being loaded into PHP via /etc/php.d/xdebug.ini. The yum method should automatically create this file for you but, if not, create it with the following inside Continue reading
Enterprise Solr getSize and Attribute Indexing Issues
If you use Magento EE and have enabled Solr for search, you may run into a few issues depending on how you customize your template. One issue is that the Enterprise Search engine for Solr does not properly handle resetting the result count when 0 results are found. The other two have to do with the way it indexes the data; it does not properly add some attributes to the index if you only have “Used for Sorting in Product Listing” enabled and/or they are the datetime attribute type on non-simple products.
The first one is relatively easy to reproduce but probably is not run into very often. On a website I was developing, the client wanted to break out the search results into individual categories and display the top few of each category results on the page. The page would then link out to each individual category to view all the results inside that category. The problem we found was that, if the previous category returned results and the current one it was outputting had none, it would still think that it had the same number of results as the previous category.
Continue reading
Catalog And Shopping Cart Pricing Rules Stacking
Ran into an issue today that I thought I would share a fix for, having catalog and shopping cart pricing rules stack their discount on each other. What do I mean?
Say you have a catalog price rule that gives a 25% discount to all items in a certain category so a product with a base price of $20 would display with a discount to $15. Now, you have a coupon code for 30% off any purchase setup as a shopping cart price rule in the admin. What happens when a user adds this item to their cart and applies the coupon code? With default Magento it runs as follows:
Continue reading
Fixing Duplicate Entry Error From Custom Order Prefix
This is something we ran into during the testing of a new Magento website. We started getting “Payment Transaction Failed Reminder” emails from the system. The client reported that they would just be presented with an error message during checkout when they tried to place the order and it would just keep returning the error no matter how many times they tried to submit it.
Looking at the email sent by the system, the error message being returned was:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘T000112’ for key 2
After investigating a bit, I found that someone else had already placed an order and taken that increment_id which is why this order was returning an error. This was a bit odd since Magento is supposed to be keeping track of these things and automatically ensure order numbers are never the same. Even looking at the sales_flat_quote table showed two entries with the same reserved_order_id which should not happen.
Continue reading