optimizing blocklayered

[sJakub]

Hi,

 

my prestashop 1.5.4.1 is running on VPS with 3 cores, 4GB and SAS HDD....

 

innodb and nginx

 

Multishop enabled for 2 domains, 2 languages, around 500 categories and 30k products with possible attributes

 

the problem is when you try reach category with blocklayered:

 

869.74 ms SELECT SQL_NO_CACHE COUNT(DISTINCT p.id_product) nbr, lpa.id_attribute_group, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title, m.name, psi.price_min, psi.price_max FROM ps_layered_product_attribute lpa INNER JOIN ps_attribute a ON a.id_attribute = lpa.id_attribute INNER JOIN ps_attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = 2 INNER JOIN ps_product as p ON p.id_product = lpa.id_product INNER JOIN ps_attribute_group ag ON ag.id_attribute_group = lpa.id_attribute_group INNER JOIN ps_attribute_group_lang agl ON agl.id_attribute_group = lpa.id_attribute_group AND agl.id_lang = 2 LEFT JOIN ps_layered_indexable_attribute_group_lang_value liagl ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = 2) LEFT JOIN ps_layered_indexable_attribute_lang_value lial ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = 2) INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_manufacturer` m ON (m.id_manufacturer = p.id_manufacturer) INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 1) WHERE a.id_attribute_group = 1 AND lpa.`id_shop` = 1 AND product_shop.active = 1 AND p.id_product IN ( SELECT id_product FROM ps_category_product cp INNER JOIN ps_category c ON (c.id_category = cp.id_category AND c.id_category = 46 AND c.active = 1)) GROUP BY lpa.id_attribute ORDER BY id_attribute_group, id_attribute
in /var/www/clients/client4/web1/web/modules/blocklayered/blocklayered.php:2858
USING FILESORT - 16740 rows browsed

 

so it is around 800-1100ms for this one query... others are OK and for example opening product page is around < 1 sec but category page is 2.5-6 sec

 

I will very appreciate any help with this issue!

Edited August 17, 2013 by drivercz (see edit history)