Experience with 18 months running prestashop and heavy increase in sales

[Scully]

Since a bit more than 18 months we are running our shop on PS 1.5.6. and would like to share some experience with it. We achieved a massive improve in out shop performance, both, technically but also in terms of sales. Our shops runs on mit100wir.ch/wirshop if someone wants to have a look at it.

 

Overall

We installed the latest available 1.5.6.2. early April 2014 and since did quite enhance our shop with modifications and one own module. We are very happy having choosen prestashop. However there also were some culprits and problems we had to sort out. What we missed and what we changed:

 

Live ticker module

We missed a front end module which was able to display messages for ongoing orders, new customers, product search etc.

 

How we fixed

We wrote an own module liveticker 1.0. The ticker shows newly ordered items, new customer regisrations, product search, products dispatched and delivered and new carts created. It grabs all data from the db and then uses javascript vticker to displays events nicely scrolling on the frontend.

 

Partial Delivery

We get a lot of orders with several items which are shipped from various supply centers and we missed the functionality of a built-in partial delivery services (including partial delivery slips as well as partial supply orders).

 

How we fixed

We changed the order_item table with an additional id_order_original field and created an external module. This module allows us to pick the items to be delivered from one order and sets the original id_order to zero for all others (non picked) items. With this trick, perstashop only knows from items which still have the original id_order but "forgets" all about the other items. After sending the supply order with delivery slip to our warehouse or supplier, the original state of id_orders will be recreated by using the 2nd field for id_orders.

 

Performance

When we exceeded ~ 1'000 products and 50+ categories, we noticed a significant drop in performance and increase in response time. This was especially the case in category pages (we have appx. 70 categories) as well as prices drop and bestseller pages. On critical pages we between 5 to 10 seconds for the onload event to be triggered.

 

How we fixed

 

Improve Database Queries

We took and deep look into how page requests are resolved in mySQL and found out, that was quite much room for improving queries. The main problem here is the following: by default prestashop retrieves all needed columns from a lot of tables to create the result set (the array of products with categories, stock available, description text and much other tables involved).

This requires a lot of mysql joins, in some cases on more than 8 or 10 tables involved.

The solution: make a first subselect which does not provide all data needed but only the product IDs. After this first step, make the query with full joins on all tables but limit this on the products which are to be displayed in that specific page request.

This changes dropped database response times from by ~ 80%.

 

Dessktop Theme and Block Top Menu (horizontal menu)

The nested category strucuture leeded to more javascript and render time in order to build the full category tree. We enhanced the blocktopmenu.php with an additional parameter called levels. The levels parameter indicates how many menu levels should be displayed in the block top menu. If set to zero, all levels are dislayed. This change with 2 levels displayed only decreased time to onload by appx. 100 ms.

 

Mobile Theme and jQuery

The performance of the the mobile theme uses jQuery in a very extense and time / CPU consuming way. We changed the mobile theme layout so only main categories are displayed. This results in much less javascript execution to resolve the category tree. This change in the mobile themes category-tree-branch.tpl saved appx. 30% of the time needed to render the mobile view.

 

Caching

Besides optimized htaccess caching rules, we also use the module express cache with a 12 hour expiration time. To ensure cache is valid at any times, we rebuild pages by cron doing a preload 4 times a day. This second step is not a built-in functionality of the express cache module. Using cache module saves appx. 30 to 50% of the web server response time since only few modules (like the product cart) are ececuted dynamically. If you use the express cache without further optimization, the performance impact might even be higher than 30 to 50%.

 

Slow controller cart

We noticed that enabling the ajax cart triggered to execute the cart controller on every single page but mostly without need since cart empty.

 

How we fixed

We enhanced the ajax-cart.js and set a cookie in order to control the behaviour of ajax cart. If cookie states an empty cart, the call to the cart controller is skipped. If the cookie indicates products in the cart or if cookie does not exist, the cart controller is executed. This change saved appx. 150 - 300 ms mostly not used response time.

 

Abandonned Cart

After having run the shop for a while, we noticed quite a lot of abandonned carts from unregistered users. Some might not have been interested in a purchase at all but some probably needed help to finish the order.

 

How we fixed

We installed the popcart module from yopixel. This module pops up a layer with a cart overview when adding products. The user can choose to add more products or to finish the order process. Prestashop 1.6. users might know this pop up. The popcart also shows company name and phone number in a demonstrative manner, allowing customers to easily call in if if the feel overstrained to finish the order. This helped a lot. Howerver we noticed some minor issues like an additional javscript which was necessary but by 80% identical with the original ajax-cart.js.

 

How we furthermore enhanced the ajax cart

We merged popcart.js with ajax-cart.js, allowing us to use the original resources but with a different functionality. We also enhanced the ajax-cart.js with an additional step which changes the background color for the cart from grey to green if there are products in the cart.

 

Overall results

Increase in performance and response time from average measured by google dashboard from 5300 ms to 600 ms.

Inital load of start page under 2 seconds. Repeated reload of start page under 0.5 seconds.

Increased sales by more than factor 2 within 6 months.

 

What about ps 1.6?

We also took a deeper look into prestashop 1.6. What we don't like: the admin interfaces need much more space. Especially we didn't like the product pages. When not using 17" or larger displays, a lot of scrolling was needed to see all details. When not working in the office but somewhere outside, this was a minus. Furthermore we realized that migratin a shop wich much enhancements would not be possible within some days.

 

Comments are welcome. best regards. Scully

 

 

[sennevb]

following this, i am curious..

[Sylvain CM]

This topic is really interesting. Please, do continue!

[Nyla C.]

Is there any way to get a copy of the module you created?

[Scully]

Sorry, but I guess not since it would require some adaptions in terms of language and also because we wouln't be able to support it for different versions and upgrades.

[Guest]

Improve Database Queries

We took and deep look into how page requests are resolved in mySQL and found out, that was quite much room for improving queries. The main problem here is the following: by default prestashop retrieves all needed columns from a lot of tables to create the result set (the array of products with categories, stock available, description text and much other tables involved).

This requires a lot of mysql joins, in some cases on more than 8 or 10 tables involved.

The solution: make a first subselect which does not provide all data needed but only the product IDs. After this first step, make the query with full joins on all tables but limit this on the products which are to be displayed in that specific page request.

This changes dropped database response times from by ~ 80%.

 

 

this is very interresting, can you please share your modifications in terms of code here with us ?

 

Best Regards

[Scully]

It would be very much to share everything. But to give you a clue or an example. In classes/product.php around line 2200 (public static function getPricesDrop)

our code looks like this:

 

        // 31.07.2015 Fix Scully to improve performance
        // $ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);
        
        // move this 3 lines upwards since alreaday needed earlier
        $front = true;
        if (!in_array($context->controller->controller_type, array('front', 'modulefront')))
            $front = false;
        
        
        // The following SQL generates a preselection of id_product with only the most important tables, less joins = less execution time
        $sql = 'SELECT DISTINCT p.id_product, IFNULL(stock.quantity, 0) as quantity, id_category_default as name
            FROM `'._DB_PREFIX_.'product_shop` p
            LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
            '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1').'
            '.Product::sqlStock('p', 0, false, $context->shop).'
            WHERE p.`active` = 1
            AND p.`show_price` = 1
            AND p.on_sale = 1
            '.($front ? ' AND p.`visibility` IN ("both", "catalog")' : '').'
            ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'
            LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;
        
        $ids_product = Db::getInstance()->ExecuteS($sql);        
        // End Fix Scully to improve performance

        // And around lines 2300, the end of the original sql does not need the limit clause any more, so it ends like this:

 

                GROUP BY product_shop.id_product
                ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'';
                // LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

[Scully]

Or and example for classes/productsale.php without further comments:

 

public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null)
{

if ($page_number < 0) $page_number = 0;
if ($nb_products < 1) $nb_products = 10;
$final_order_by = $order_by;
$order_table = '';

// Scully fix for all possible order by fields
if (is_null($order_by) || is_null($order_way) || $order_by == 'position' || $order_by == 'date_upd' || $order_by == 'quantity' ) {
    $order_by = 'sales'; $order_way = 'DESC'; }
if ($order_by == 'date_add' OR $order_by == 'date_upd' )  $order_table = 'product_shop';
if ($order_by == 'price')  $order_table = 'product_shop';

$sql_groups = '';
if (Group::isFeatureActive())
{
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'WHERE cp.`id_product` IS NOT NULL AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
}
$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;

// Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
/*
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT cp.`id_product`
FROM `'._DB_PREFIX_.'category_group` cg
INNER JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
'.$sql_groups);
*/

// Scully 05.08.2015 new faster preselect
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT DISTINCT id_product
FROM `'._DB_PREFIX_.'product_sale`'
);

$ids = array();
foreach ($products as $product)
if (Validate::isUnsignedId($product['id_product']))
$ids[$product['id_product']] = 1;
$ids = array_keys($ids);
$ids = array_filter($ids);
sort($ids);
$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';


//Main query
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
MAX(image_shop.`id_image`) id_image, il.`legend`,
ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
p.is_new AS new
FROM `'._DB_PREFIX_.'product_sale` ps
LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product`
'.Shop::addSqlAssociation('product', 'p', false).'
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
ON p.`id_product` = pl.`id_product`
AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`)
AND tr.`id_country` = '.(int)Context::getContext()->country->id.'
AND tr.`id_state` = 0
LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
'.Product::sqlStock('p').'
WHERE product_shop.`active` = 1
AND product_shop.`price` > 0
AND product_shop.`available_for_order` = 1
AND p.`visibility` NOT IN ( \'none\',\'search\' )
AND p.`id_product` IN ('.$ids.')
GROUP BY product_shop.id_product
ORDER BY '.(!empty($order_table) ? '`'.pSQL($order_table).'`.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);

if ($final_order_by == 'price') {
   Tools::orderbyPrice($result, $order_way);
}
if (!$result)
return false;
return Product::getProductsProperties($id_lang, $result);
}

[Guest]

Thanks a lot for Sharing !!

 

[Alphaloc]

Great post and work! Thanks for sharing