Prestashop 1.2 performance bug - customer groups

performance sufferings

If your prestashop 1.2 database is populated with few hundred customer records, your e-shop may be in real danger of very bad performance. The problem lies in lack of properly set MySQL indexes and primary keys. Within the time, your shop may be unusable, because MySQL server will harvest enormous piles of data, leaving the end-user to wait for the browser response half a minute or so. This is a serious threat which has been fixed in newer prestashop versions. But some people still run the 1.2 prestashop version since 2009/2010 was the time when prestashop was becoming very popular. This version is concerned, however, to be one of the worst versions of prestashop ever.

the problem

Group feature is used to define reductions, price displaying methods and some price-related stuff that refer to only a part of the shop. This may be: chosen customers (ps_customer_group table being used for that), chosen product categories (ps_category_group) and so on. Although, it is not commonly used by merchants. Fortunately not, because it makes performance extremely bad.

Below is an example of a bad query. I fetched it using MySQL slow logs:

# Query_time: 20.056626  Lock_time: 0.000182 Rows_sent: 1  Rows_examined: 372383                                                                                 
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,i.`id_image`, il.`legend`, t.`rate`                                                                                                      
FROM `ps_product` p                                                                                                                              
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 3)
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 3)
LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= '2012-10-14' AND `reduction_to` >= '2012-10-14'))
AND p.`active` = 1
AND (cg.`id_customer` = 223 OR ctg.`id_group` = 1)
ORDER BY RAND() LIMIT 1;

Such query took me 20 seconds and in my database I've got around 500 orders, less than 300 customers and over 1700 products only. Now I call the MySQL EXPLAIN:

+----+-------------+-------+--------+-----------------------------------------+--------------------+---------+--------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                           | key                | key_len | ref                                  | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------+--------------------+---------+--------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | ALL    | PRIMARY,reduction_date                  | NULL               | NULL    | NULL                                 | 1702 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | pl    | eq_ref | product_lang_index,id_lang              | product_lang_index | 8       | ad9bis_prestashop.p.id_product,const |    1 |                                              |
|  1 | SIMPLE      | cp    | ref    | category_product_index,product_category | product_category   | 4       | ad9bis_prestashop.p.id_product       |    3 | Using where                                  |
|  1 | SIMPLE      | ctg   | ref    | PRIMARY,group_category                  | PRIMARY            | 4       | ad9bis_prestashop.cp.id_category     |    1 | Using index                                  |
|  1 | SIMPLE      | i     | ref    | image_product                           | image_product      | 4       | ad9bis_prestashop.p.id_product       |    3 |                                              |
|  1 | SIMPLE      | cg    | index  | PRIMARY,customer_login,id_customer      | PRIMARY            | 8       | NULL                                 |  238 | Using where; Using index; Using join buffer  |
|  1 | SIMPLE      | il    | eq_ref | image_lang_index                        | image_lang_index   | 8       | ad9bis_prestashop.i.id_image,const   |    1 |                                              |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                                 | PRIMARY            | 4       | ad9bis_prestashop.p.id_tax           |    1 |                                              |
+----+-------------+-------+--------+-----------------------------------------+--------------------+---------+--------------------------------------+------+----------------------------------------------+
8 rows in set (0.02 sec)

As you can see, ALL type is used to seek ps_product records. Afterwards, index type is used to join ps_customer_group records. Such queries are not optimized at all (that's why all old prestashop applications shoud be upgraded to newer versions).

the clean solution

I spent some time trying to fix proper indexes on database tables. It's quite frustrating, because there are almost 150 tables and you should check most of them. Of course, the biggest problem is on the m:n relational tables (ps_product_tag, ps_category_group, etc.) but even the product table is missing a proper index then reduction_from and reduction_to column values are being compared (as you can see in the example above).

So, instead of the clean solution, I chose...

the brute-force solution

Finally, I decided to cut off (i.e. completely remove) the entire customer group functionality from PHP code level, because neither of two prestashop application owners I work with use the feature and this gave me 100% that prestashop will work as fast as possible. Take a look at the code:

$sql = '...
LEFT JOIN `'._DB_PREFIX_.'manufacturer` as m ON (m.`id_manufacturer`= p.`id_manufacturer`)
LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
'.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
WHERE ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
AND m.`id_manufacturer` = '.intval($manufacturer['id_manufacturer']).'
GROUP BY p.`id_product`';

Look at the fragment using the cookie object:

$cookie->id_customer ? '--sql wipe out code--' : ''

PHP checks if user is logged in. If the user is not loggen in (anonymous), PHP won't know which group does this customer belong to, so it ommits the bad SQL clauses. And this makes sense - for anonymous users, prestashop 1.2 works like a charm.

You may save yourself a lot of pain by getting rid of those SQL clauses. Grep the classes directory for the cookie phrase:

grep -Rn '$cookie->id_customer ?' .

You'll have to modify just 6 classes:

• Manufacturer.php
• Product.php
• ProductSale.php
• Search.php
• Supplier.php
• Tag.php

Below I dump my git repository diff that applies those changes:

commit 32f6a9facaf77ca9c8326baf288187889b3e1fc4
Author: Tomasz Ducin
Date:   Sun Oct 14 12:07:09 2012 +0200

    customer_group removed entirely from all sql queries

diff --git a/classes/Manufacturer.php b/classes/Manufacturer.php
index 1b9d5df..e1cabe6 100644
--- a/classes/Manufacturer.php
+++ b/classes/Manufacturer.php
@@ -176,8 +176,8 @@ class        Manufacturer extends ObjectModel
                     LEFT JOIN `'._DB_PREFIX_.'manufacturer` as m ON (m.`id_manufacturer`= p.`id_manufacturer`)
                     LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                     INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-                    '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
-                    WHERE ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+                    './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
+                    WHERE ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
                     AND m.`id_manufacturer` = '.intval($manufacturer['id_manufacturer']).'
                     GROUP BY p.`id_product`';
                 $result = Db::getInstance()->ExecuteS($sql);
@@ -251,9 +251,9 @@ class        Manufacturer extends ObjectModel
             FROM `'._DB_PREFIX_.'product` p
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.id_manufacturer = '.intval($id_manufacturer).'
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)'
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)'
             .($active ? ' AND p.`active` = 1' : '')
             .' GROUP BY p.`id_product`');
             return intval(sizeof($result));
@@ -270,9 +270,9 @@ class        Manufacturer extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE p.`id_manufacturer` = '.intval($id_manufacturer).($active ? ' AND p.`active` = 1' : '').'
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY '.(($orderBy == 'id_product') ? 'p.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).' 
         LIMIT '.((intval($p) - 1) * intval($n)).','.intval($n);
diff --git a/classes/Product.php b/classes/Product.php
index 3f54c90..0ffba15 100644
--- a/classes/Product.php
+++ b/classes/Product.php
@@ -1006,10 +1006,10 @@ class        Product extends ObjectModel
             LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.`active` = 1
             AND DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             GROUP BY p.`id_product`
             ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
             LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
@@ -1041,14 +1041,14 @@ class        Product extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'tax` t ON t.`id_tax` = p.`id_tax`
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
         '.((!$beginning AND !$ending) ?
             'AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= \''.pSQL($currentDate).'\' AND `reduction_to` >= \''.pSQL($currentDate).'\'))'
         :
             ($beginning ? 'AND `reduction_from` <= \''.pSQL($beginning).'\'' : '').($ending ? 'AND `reduction_to` >= \''.pSQL($ending).'\'' : '')).'
         AND p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         ORDER BY RAND()');
 
         if ($row)
@@ -1090,9 +1090,9 @@ class        Product extends ObjectModel
             FROM `'._DB_PREFIX_.'product` p
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE (p.`reduction_price` > 0 OR p.`reduction_percent` > 0)
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             AND p.`active` = 1';
             $result = Db::getInstance()->getRow($sql);
             return intval($result['nb']);
@@ -1108,14 +1108,14 @@ class        Product extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
         '.((!$beginning AND !$ending) ?
             'AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= \''.pSQL($currentDate).'\' AND `reduction_to` >= \''.pSQL($currentDate).'\'))'
         :
             ($beginning ? 'AND `reduction_from` <= \''.pSQL($beginning).'\'' : '').($ending ? 'AND `reduction_to` >= \''.pSQL($ending).'\'' : '')).'
         AND p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'`'.' '.pSQL($orderWay).'
         LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts);
diff --git a/classes/ProductSale.php b/classes/ProductSale.php
index a55ec36..3016557 100644
--- a/classes/ProductSale.php
+++ b/classes/ProductSale.php
@@ -71,9 +71,9 @@ class        ProductSale
         LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
         LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
@@ -112,9 +112,9 @@ class        ProductSale
         LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (cl.`id_category` = p.`id_category_default` AND cl.`id_lang` = '.intval($id_lang).')
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY sales DESC
         LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
diff --git a/classes/Search.php b/classes/Search.php
index 6811f69..1faaf18 100644
--- a/classes/Search.php
+++ b/classes/Search.php
@@ -180,10 +180,10 @@ class Search
             LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.intval($id_lang).')
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE '.implode(' AND ', $whereArray).'
             AND p.active = 1
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             GROUP BY p.`id_product`
             ORDER BY position DESC
             LIMIT 10';
@@ -192,7 +192,7 @@ class Search
         
         $queryResults = '
         SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
-        t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name '.($cookie->id_customer ? ', cg.`id_group`' : '').'
+        t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name './*($cookie->id_customer ? ', cg.`id_group`' : '').*/'
         '.$score.'
         FROM '._DB_PREFIX_.'product p
         LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
@@ -202,10 +202,10 @@ class Search
         LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE '.implode(' AND ', $whereArray).'
         AND p.active = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         '.($orderBy ? 'ORDER BY  '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'
         LIMIT '.intval(($pageNumber - 1) * $pageSize).','.intval($pageSize);
diff --git a/classes/Supplier.php b/classes/Supplier.php
index 0052572..904d15d 100644
--- a/classes/Supplier.php
+++ b/classes/Supplier.php
@@ -109,8 +109,8 @@ class        Supplier extends ObjectModel
                     LEFT JOIN `'._DB_PREFIX_.'supplier` as m ON (m.`id_supplier`= p.`id_supplier`)
                     LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                     INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-                    '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
-                    WHERE ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+                    './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
+                    WHERE ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
                     AND m.`id_supplier` = '.intval($supplier['id_supplier']).'
                     GROUP BY p.`id_product`';
                 $result = Db::getInstance()->ExecuteS($sql);
@@ -170,9 +170,9 @@ class        Supplier extends ObjectModel
             FROM `'._DB_PREFIX_.'product` p
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.id_supplier = '.intval($id_supplier).'
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)'
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)'
             .($active ? ' AND p.`active` = 1' : '')
             .' GROUP BY p.`id_product`');
             return intval(sizeof($result));
@@ -189,9 +189,9 @@ class        Supplier extends ObjectModel
                 LEFT JOIN `'._DB_PREFIX_.'supplier` s ON s.`id_supplier` = p.`id_supplier`
                 LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                 INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-                '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+                './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.`id_supplier` = '.intval($id_supplier).($active ? ' AND p.`active` = 1' : '').'
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             GROUP BY p.`id_product`
             ORDER BY '.(($orderBy == 'id_product') ? 'p.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).' 
             LIMIT '.((intval($p) - 1) * intval($n)).','.intval($n);
diff --git a/classes/Tag.php b/classes/Tag.php
index d2ebbeb..cf272ea 100644
--- a/classes/Tag.php
+++ b/classes/Tag.php
@@ -119,10 +119,10 @@ class Tag extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'product` p ON p.id_product = pt.id_product
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE id_lang = '.intval($id_lang).'
         AND p.active = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY t.id_tag
         ORDER BY times DESC
         LIMIT 0, '.intval($nb));

If you ever need to undo this change, just uncomment those lines and you're done (but think twice before you go back to customer group joins).

Prestashop Category Tree as a Nested Set

Nested set is one of the approaches to represent a tree in relational databases.

how it works

Take a look at the example pictures from wikipedia:

This is just a tree with some numbers attached to node labels. Those numbers make a lot more sense if the tree is presented a nested sets (sets inside sets):

Note that reading all numbers from left to right is the ordered sequence of integer numbers. This is how the nested set are built and how the hierarchy is restored basing on those numbers.

Let's say, we've got a leaf node inside a tree. If this is just a normal tree (not a nested set), walking through all parents until reaching the root node will cost one subquery per each parent. But if we have a nested set, we can do this all with just one query by manipulating the left/right values of the sets - this is all what nested sets are here for.

parent of (a parent of (...)) a node

Walking from a specific node to the root node of the tree is used in the product display page. The breadcrumb section shows the path from the root node to the main category the product belongs to (product.id_category_default in the database). The Product::getParentCategories() method is called to find all parents of a chosen category:

WHERE c.nleft < (int)$category['nleft']
AND c.nright > (int)$category['nright']
ORDER BY c.nleft

You can imagine this as widening the left/right range. Take a look at the picture above: Jackets have the range of 6 : 7 (7 - 6 = 1 which is the smallest possible subset = the leaf). The left value will be decreased (the smaller one) while the right value will be increased (the bigger one). The parent of Jackets is Suits with the range of 3 : 8 (8 - 3 = 5). Men's category, the parent of Suits, will be found in the same way - decreasing the left value and increasing the right value, 2:9. If the left value equals 1, you're already at the root node.

children of a node

Finding all children of a specific node is done in the opposite way. You take all subsets of a given set. As everything is ordered, all nested subsets will have their left/right values inside the range of the chosen node:

WHERE c.nleft > (int)$category['nleft']
AND c.nright < (int)$category['nright']
ORDER BY c.nleft

In other words, we'll get all nodes that are situated between the left and the right border of the chosen node area. All nodes that are beyond this area are not the children of the chosen category.

Finding all children of a category tree node is used in the Category::getAllChildren() method.

nested sets in prestashop

Such approach was introduced in category structure in prestashop since version 1.4.0.5, released on December 22, 2010.

For anyone having older versions of the platform, prestashop provides special functionality which fills up all nested set data. It is implemented in Category class in Category::regenerateEntireNtree() method.

upgrading

Each new version of prestashop is provided with a small SQL script which enables to update the database structure to fit new functionalities - take a look at the install/sql/upgrade directory. It works just like doctrine migrations in symfony 1.x. The 1.4.0.5.sql script from the directory above includes the following lines:

ALTER TABLE `PREFIX_category` ADD `nleft` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `level_depth`;
ALTER TABLE `PREFIX_category` ADD `nright` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `nleft`;
ALTER TABLE `PREFIX_category` ADD INDEX `nleftright` (`nleft`, `nright`);

Those lines create 2 columns which store the left/right values which will be used to traverse the tree from child nodes to their parents.

database performance

Selects are faster with such structure. But be aware that each modification you make on any category tree node will fire regenerating entire nested set values - prestashop doesn't check where the modified node is - it builds the nested set from scratch (Category::regenerateEntireNtree()). If you have a really big tree, inserting a new category node can take quite a lot of time.

svn dump subrepo

Subversion tools enable you to dump only chosen parts of the original repository. This may be especially useful, when you want to separate a smaller project out from a bigger thing. svnadmin dump will be used along with svndumpfilter command. svndumpfilter must be run using one of the following subcommands: include or exclude. Depending on what is easier, you may specify which path prefixes (or directories) will be included into new subrepo dump - or which will be excluded.

For example, let's assume our original repository structure looks like the following:

/abc
  some content
/def
  some content
/ghi
  some content
/jkl
  some content

If we run:

$ svndumpfilter include /abc /def # pseudo command

our subrepo will consist of:

/abc
  some content
/def
  some content

but if we run:

$ svndumpfilter exclude /abc # pseudo command

our subrepo will consist of:

/def
  some content
/ghi
  some content
/jkl
  some content

I wrote pseudo command, since you have to stream the original repository first, so that svndumpfilter can filter its content. Real commands would look like:

$ svnadmin dump /home/tomasz/svn/original_repo | svndumpfilter include /abc /def --drop-empty-revs --renumber-revs > subrepo.svndump

$ svnadmin dump /home/tomasz/svn/original_repo | svndumpfilter exclude /abc --drop-empty-revs --renumber-revs > subrepo.svndump

Now let's take a look at some svndumpfilter options:

--drop-empty-revs

If filtering causes any revision to be empty (i.e., causes no change to the repository), removes these revisions from the final dump file.

--renumber-revs

Renumbers revisions that remain after filtering.

Thanks to above options, the result repository dump will look as if it always consisted of the chosen parts (no empty commits).