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