Symfony world: Prestashop 1.2 performance bug

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).

4 comments:

LoïcOctober 17, 2012 at 6:15 PM
Hi Tomasz,
I got to your blog post through symfony blog quotes and feel disappointed about the last topics dealing with prestashop, isn't the name of your blog "symfony world" ?
By the way, prestashop 1.2 is really really old, I think people having this issue should really consider upgrading to something newer.
Good luck
LoïcOctober 18, 2012 at 11:39 AM
Great, have fun with ps1.5, we had some troubles in putting up some stores as versions 1.5.0.17 and .1.0 still have issues I would not call minor
This is what makes us miss sf in fact, code is not as reliable, extendable that what we are used to with sf, and updates are tough to manage when you start overriding core elements.
Lets see if PS will shoose sf2 for their next major release ;)
TomaszOctober 18, 2012 at 9:55 PM
Basing future versions of prestashop on ANY framework would be reasonable (zend would not be bad at all, comparing to prestaPHP :P). But I'm afraid that prestashop team won't even consider it...

Symfony world

Prestashop 1.2 performance bug - customer groups

performance sufferings

the problem

the clean solution

the brute-force solution

4 comments:

Blog Archive

Followers