Fight with speed now SQL problem

tczaude · June 10, 2013

Hi

I fight with performance with Prestashop 1.5.1 and I am on god way but i have problem with this query somebody havy any idea on improvement this query ?? This take 80 % time on queering :/

381.21 ms SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 6 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`) LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop_group = 11 AND stock.id_shop = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 6 AND cl.id_shop = 1 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 6 AND pl.id_shop = 1 ) LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 6) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 1 AND cp.`id_category` = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product ORDER BY cp.`position` ASC LIMIT 0,8

in /var/customers/webs/Termokontrol/sklep/classes/Category.php:651

tuk66 · July 15, 2013

I suggest to upgrade to the latest version of PrestaShop as versions before 1.5.4 was buggy.

Use EXPLAIN or EXPLAIN EXTENDED before your query in phpMyAdmin to find a problem.

tczaude · July 15, 2013

I try do something with this but i must setup development envritoment with mysql profiling.

This query is amaizning

ironwill · November 26, 2013

SAME ISSUE WITH CATEGORY CLASS LINE 651

Load time: 50.236s

You'd better run your shop on a toaster

config: 39ms
constructor: 0ms
init: 15ms
checkAccess: 0ms
setMedia: 3ms
postProcess: 0ms
initHeader: 0ms
initContent: 49.988s
initFooter: 20ms
display: 171ms

Hook processing: 145ms / 2.24 Mb

displayHeader: 51ms / 1.25 Mb
displayTop: 29ms / 0.27 Mb
displayRightColumn: 22ms / 0.26 Mb
displayFooter: 20ms / 0.19 Mb
displayLeftColumn: 17ms / 0.15 Mb
moduleRoutes: 5ms / 0.12 Mb
actionDispatcher: 0ms / 0 Mb
actionProductListOverride: 0ms / 0 Mb
actionFrontControllerSetMedia: 0ms / 0 Mb
DisplayOverrideTemplate: 0ms / 0 Mb

Memory peak usage: 11.9 Mb

config: 1.79 Mb (1.8 Mb)
constructor: 0 Mb (1.8 Mb)
init: 0.78 Mb (2.6 Mb)
checkAccess: 0 Mb (2.6 Mb)
setMedia: 0.04 Mb (2.6 Mb)
postProcess: 0 Mb (2.6 Mb)
initHeader: 0 Mb (2.6 Mb)
initContent: 8.02 Mb (11.7 Mb)
initFooter: 0.2 Mb (11.7 Mb)
display: 0.66 Mb (11.9 Mb)

Total cache size (in Cache class): 0.53 Mb

DB type: DbPDO
SQL Queries: 150 queries
Time spent querying: 49.779s

Included files: 177
Size of included files: 2.11 Mb

Globals (> 1 Ko only): 109 Ko

_LANG ≈ 58.5 Ko
_MODULES ≈ 28.9 Ko
_SERVER ≈ 11.5 Ko
_REQUEST ≈ 2.1 Ko
_COOKIE ≈ 2.1 Ko
_MODULE ≈ 1 Ko

Stopwatch (with SQL_NO_CACHE) (total = 150)49625.491 ms SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`) LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = 1 ) LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 1 AND cp.`id_category` = 11 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product ORDER BY cp.`position` asc LIMIT 0,10
in /home/content/01/6091401/html/classes/Category.php:651
USING FILESORT - 43824 rows browsed10.885 ms SELECT SQL_NO_CACHE c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description FROM `ps_category` c INNER JOIN ps_category_shop category_shop ON (category_shop.id_category = c.id_category AND category_shop.id_shop = 1) LEFT JOIN `ps_category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = 1 AND cl.id_shop = 1 ) LEFT JOIN `ps_category_group` cg ON (cg.`id_category` = c.`id_category`) WHERE `id_parent` = 11 AND `active` = 1 AND cg.`id_group` =2 GROUP BY c.`id_category` ORDER BY `level_depth` ASC, category_shop.`position` ASC
in /home/content/01/6091401/html/classes/Category.php:529
USING FILESORT - 7 rows browsed
Useless GROUP BY need to be removed

tczaude · November 27, 2013

Hi You don't have problem with mysql but with webserver and php

try use some cache method or do migrate on VPS

Dh42 · November 27, 2013

Link me to your shop, I will take a look.

lozaria · December 10, 2013

Same problem with me. SELECT SQL_NO_CACHE p.*, product_shop.*.... takes 168.187 ms classes/Category.php:651

This makes my website slow.

Dh42 · December 10, 2013

How many categories do you have?

bellini13 · December 11, 2013

include your Prestashop version and details about your store if you are going to include yourself in the problem.

lozaria · January 4, 2014

I'm using PrestaShop™ 1.5.6.0

I have about 4000 products and 350 categories.

Dh42 · January 5, 2014

What kind of hosting do you tow have? Also provide links to your shops.

kuskov · March 12, 2014

I have the same problem, but only with one category. All other categories are somehow faster:

Shop URL: http://new.vikav.ee/ru/103-cookingblocks
Version: PrestaShop™ 1.5.6.2

Hosting: eShop L (http://alfahosting.de/vergleich-onlineshops/)

The funniest thing, that exactly the same category on productive website works without problems:
URL: http://www.vikav.ee/en/103-cooking-block

Version: PrestaShop™ 1.4.11.0

Is tere a fix?

tuk66 · March 14, 2014

Nice debug messages on http://new.vikav.ee/ru/103-cookingblocks. What is it? A module?

kuskov · March 14, 2014

This is a simple profiling=true

kuskov · March 18, 2014

I have found out, that the attributes searching slows down the query. I have removed the selection of attributes and the query is very fast now:


		/*$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
					il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`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 AS new, product_shop.price AS orderprice
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').'
				LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
				ON (p.`id_product` = pa.`id_product`)
				'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
				'.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				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 (image_shop.`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`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
					.($active ? ' AND product_shop.`active` = 1' : '')
					.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
					.($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '')
					.' GROUP BY product_shop.id_product';
*/

$sql = 'SELECT p.*, product_shop.*, 0 as quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
					il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`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 AS new, product_shop.price AS orderprice
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').'

				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				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 (image_shop.`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`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
					.($active ? ' AND product_shop.`active` = 1' : '')
					.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
					.($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '')
					.' GROUP BY product_shop.id_product';

tczaude · March 19, 2014

Ok i look how is look i new version

This is good idea with this query

vitochueng · May 4, 2014

I have found out, that the attributes searching slows down the query. I have removed the selection of attributes and the query is very fast now:


		/*$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
					il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`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 AS new, product_shop.price AS orderprice
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').'
				LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
				ON (p.`id_product` = pa.`id_product`)
				'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
				'.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				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 (image_shop.`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`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
					.($active ? ' AND product_shop.`active` = 1' : '')
					.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
					.($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '')
					.' GROUP BY product_shop.id_product';
*/

$sql = 'SELECT p.*, product_shop.*, 0 as quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
					il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`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 AS new, product_shop.price AS orderprice
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').'

				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				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 (image_shop.`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`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
					.($active ? ' AND product_shop.`active` = 1' : '')
					.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
					.($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '')
					.' GROUP BY product_shop.id_product';

Thanks a lot! Your method makes my site a lot faster...

Zitoun · June 4, 2014

Good evening,

I received recently a "slowlog" notice by my host Infomaniak mentionning the same problem for my website under PS1.5.6.2 and found your solution.

Unfortunately, the above fix by Kuskov solved the slow SQL but it also seemed to removed some (???) prices for products with attributes.

To solve this other problem due to an error in columns `cache_default_attribute` I found this script on http://forge.prestashop.com/browse/PSCFV-6041:

UPDATE `ps_product` SET `cache_default_attribute` = 0;
UPDATE `ps_product_shop` SET `cache_default_attribute` = 0;
UPDATE `ps_product` AS p, `afrips_product_attribute` AS pa SET p.`cache_default_attribute` = pa.`id_product_attribute` WHERE p.`id_product` = pa.`id_product` AND pa.`default_on` = 1;
UPDATE `ps_product_shop` AS p, `afrips_product_attribute` AS pa SET p.`cache_default_attribute` = pa.`id_product_attribute`WHERE p.`id_product` = pa.`id_product` AND pa.`default_on` = 1;

So far it apparently worked.

Edited June 6, 2014 by Zitoun (see edit history)

patrmich · June 23, 2014

Hi Kuskov,

Thank you very much indeed for the solution you found to shorten the query on product_list pages.

It saves plenty of time in loading such pages.

My shop is built under Prestashop 1.5.6.2

I should have some questions :

1- In the Prestashop original category.php file, why was the selection of attribute included in the query on product_list pages ?

Was it included for webmasters who decides to have the "add to cart button" on each product of the product_list page ?

As to me, the "add to cart button" is only displayed on the product page.

What do you think about it ?

2- The loading of the "Best selling" items page also takes a lot of time. The beginning of the related slow query is as follows :

SELECT SQL_NO_CACHE 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......

Would you know how to shorten the query in ./classes/ProductSale.php ?

(On Prestashop 1.4 the display of the best selling item page was quick)

Thank you very much in advance for any reply.

Patrick

kuskov · December 22, 2014

As for me, those queries:

UPDATE `ps_product` SET `cache_default_attribute` = 0;

UPDATE `ps_product_shop` SET `cache_default_attribute` = 0;

UPDATE `ps_product` AS p, `ps_product_attribute` AS pa SET p.`cache_default_attribute` = pa.`id_product_attribute`
WHERE p.`id_product` = pa.`id_product` AND pa.`default_on` = 1

UPDATE `ps_product_shop` AS p, `ps_product_attribute` AS pa SET p.`cache_default_attribute` = pa.`id_product_attribute`
WHERE p.`id_product` = pa.`id_product` AND pa.`default_on` = 1

Don't help anything, they even don't speed up the query. The problem of this query happens, when your products have a lot of different pricing groups and joining them makes the database die in the hell... In the PS1.6 there are no problems anymore and i don't know why...

tufik · January 30, 2015

Hi all,

I have this exact same problem, but removing the attributes part here, it removes the possibility to combine different attributes under a single product. Without that part of the query, the different attributes for a single product are not loaded anymore and that is very important for me.

Is there a way to keep the attributes and fix this query to run appropriately since it is really slowing my site?

tufik · January 31, 2015

Anyone any suggestion about fixing this query without removing the attributes part?

tufik · February 1, 2015

Anyone?

zapalm · February 22, 2015

modifying core classes to remove selection of attributes from the sql-queries is a bad idea but to optimize well heavy sql-queries is hard, for example, a query from Catagory::getProducts() method.

in PS1.6 will be the same problem as with PS1.5 when a shop has a great number of categories|products|combinations.

more simply way for a solution is to enable caching system in a shop, but caching subsystem of PS1.5 is not works. in PS1.6 it is works but there are some other cases that make caching subsystem work worse, so you will not see any growing performance after some time of using it.

another additional way for the solution is to use more powerful server, for example, a non-cheap VDS (VPS) with SDD-disk.

i have some experience with this problem.

if you have PS1.5 then you should update it to the last of PS1.6.

then modifications in some files should be made (in core classes and may be also in some classes of modules) because there are sql-queries that is unique on each pages load and therefore they can not to be cached. to detect these queries the profiling tool of PS should be helpful...

my results of optimizing a shop (PS1.5.4.1) with 200 categories, 3 250 products and 40 000 combinations.

i have used a simple 'file caching subsystem' of PS (CacheFs). all time measurement that described below is average time.

before optimizing the heaviest page was loaded in 8.307 seconds.

after optimizing the same page is loading in 1.072 seconds on the same server, but on another server (VDS with SSD-disk, $20) this page is loading in 0,223 seconds.

i think it is a great result and almost simple/cheap, but i have spent many time to get this result...

good luck!

selectshop.at · February 22, 2015

I'm fighting with speed problems since PS version 1.3. and my experience is: Each shop, regardless of the verison 1.4. 1.5 or 1.6, with more than 2.000 products should be use a server caching module (APC, XCache, memcache, opcode in combination with fast-cgi or not, nginx, etc.). Furthermore you should host on VPS. My shop has about 10.000 products and loading time is about 3 seconds.

Edited February 22, 2015 by selectshop.at (see edit history)

mauroagr · April 30, 2015

Hi Selectshopt.at

How server specification you use the and prestashop with 10000 products?

Thanks

Mauro

selectshop.at · April 30, 2015

Please check on Portuguese Forum: https://www.prestashop.com/forums/topic/236128-tutorial-servidor-configura%C3%A7%C3%A3o-m%C3%ADnima-para-prestashop/

BTW in the meantime I moved to a VPS Cloud package (Debian + opcode cache + fast-cgi + ngnix) installed and configured by myself, but with same min. requisites stated there.

Edited April 30, 2015 by selectshop.at (see edit history)

Scully · July 31, 2015

We had the exactly same problem with more than 1000 prices drop products and found a solution to reduce our already optimized execution time from appx. 3 seconds to ~ 0.5 seconds. The problem was found in /classes/product.php and there in the function getPricesDrop. The default behavior in

1.5.6. is: first get an array of id_products within a certain date, this is done in appx. line2230.

$ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);

Since we do not use valid from and valid to dates in products, we simply changed the code as follows.

From the original lines 2230 to line 2244:

$ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);

$tab_id_product = array();

foreach ($ids_product as $product)

if (is_array($product))

$tab_id_product[] = (int)$product['id_product'];

else

$tab_id_product[] = (int)$product;

$front = true;

if (!in_array($context->controller->controller_type, array('front', 'modulefront')))

$front = false;

$groups = FrontController::getCurrentCustomerGroups();

$sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

To this new code:

// 31.07.2015 Fix 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

FROM `'._DB_PREFIX_.'product` p

'.Shop::addSqlAssociation('product', '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).'

LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (

p.`id_product` = pl.`id_product`

AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'

)

WHERE product_shop.`active` = 1

AND product_shop.`show_price` = 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 Schnellmann to improve performance

$tab_id_product = array();

foreach ($ids_product as $product)

if (is_array($product))

$tab_id_product[] = (int)$product['id_product'];

else

$tab_id_product[] = (int)$product;

$groups = FrontController::getCurrentCustomerGroups();

$sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

// END OF FIX

Short explanation: This code does replace the getProductIdByDate call but also delivers an array of id_product which are valid for the selected products including the possible order by and orderway. It restricts the result to the N products (typically 10 per page).

One more small change is needed on the original lines appx. around 2303 and 2304. Change from:

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;

To new:

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;

Note the ending of the first line!! The LIMIT clause may not take effect here any more since the LIMIT has already done in the preselect before.

You might want to check out the response times for our prices drop page here:

http://mit100wir.ch/wirshop/wir-aktionen

Known limitations

If you youse the product valid from and product valid to date ranges in order to automatically activate / deactivate products, you would have to do one step more in order to preserve this functionality.

best regards, Scully

Fight with speed now SQL problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites