[ASK] Blocklayered.php slow SQL

[eSzeL]

Dear Community!

 

I would like to ask some help:

 

I see this in my slow query log at 6:58, 10:21, 10:52, 10:53, 11:03, 12:03, 12:28 etc... This is the only query in my log (log has every query with minimum 3 seconds runtime).

 

(This SQL comes from module blocklayered.php.)

# Query_time: 6.165144  Lock_time: 0.000448  Rows_sent: 67  Rows_examined: 2480513  Rows_affected: 0
# Bytes_sent: 4142
SET timestamp=1411727330;
SELECT COUNT(DISTINCT p.id_product) nbr, lpa.id_attribute_group,
                                        a.color, al.name attribute_name, agl.public_name attribute group_name , lpa.id_attribute, ag.is_color_group,
                                        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 = 1
                                        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 = 1
                                        LEFT JOIN ps_layered_indexable_attribute_group_lang_value liagl
                                        ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = 1)
                                        LEFT JOIN ps_layered_indexable_attribute_lang_value lial
                                        ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = 1)  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 AND psi.id_shop=1)
WHERE a.id_attribute_group = 1 AND lpa.`id_shop` = 1 AND product_shop.active = 1 AND product_shop.`visibility` IN ("both", "catalog")
                                        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.nleft >= 5
                                                AND c.nright <= 6
                                                AND c.active = 1)
                                        )

                                        GROUP BY lpa.id_attribute
                                        ORDER BY ag.`position` ASC, a.`position` ASC;

This is the explain:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	c	range	PRIMARY,nleftright,nleftrightactive,nright,nleft	nright	4	NULL	15	Using index condition; Using where; Using temporary; Using filesort; Start temporary
1	SIMPLE	cp	ref	PRIMARY,id_product	PRIMARY	4	szertarsporthu.c.id_category	36	Using index
1	SIMPLE	p	eq_ref	PRIMARY	PRIMARY	4	szertarsporthu.cp.id_product	1	End temporary
1	SIMPLE	product_shop	eq_ref	PRIMARY	PRIMARY	8	szertarsporthu.cp.id_product,const	1	Using where
1	SIMPLE	psi	eq_ref	PRIMARY,id_currency,price_min,price_max	id_currency	12	const,szertarsporthu.cp.id_product,const	1	Using index condition; Using where
1	SIMPLE	sav	ref	product_sqlstock,id_shop,id_product	product_sqlstock	4	szertarsporthu.cp.id_product	1	Using index condition; Using where
1	SIMPLE	m	ALL	PRIMARY	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	a	ref	PRIMARY,attribute_group	attribute_group	4	const	307	NULL
1	SIMPLE	al	eq_ref	PRIMARY,id_lang	PRIMARY	8	szertarsporthu.a.id_attribute,const	1	NULL
1	SIMPLE	lpa	ref	id_attribute	id_attribute	4	szertarsporthu.a.id_attribute	18	Using where
1	SIMPLE	liagl	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	agl	eq_ref	PRIMARY	PRIMARY	8	szertarsporthu.lpa.id_attribute_group,const	1	NULL
1	SIMPLE	lial	eq_ref	PRIMARY	PRIMARY	8	szertarsporthu.a.id_attribute,const	1	Using where
1	SIMPLE	ag	ALL	PRIMARY	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)

SQL result:

http://ctrlv.in/433688

 

Explain result:

http://ctrlv.in/433689

I dont know what my visitors do to achieve this, but I would like to solve that..

 

The server is a 4 core / 8gb ram / raid machine. Ps_product has 8236 rows, webpage url is szertar (no space here) sport dot hu ... <- I dont want to be in search results..

 

What should I do? Should I tune MySQL, for example increase join buffer?

 

Thank You for Your help in advance!

 

Sincerely,

eSzeL

Edited September 26, 2014 by eSzeL (see edit history)

[eSzeL]

SQL result:

http://ctrlv.in/433688

 

Explain result:

http://ctrlv.in/433689

[GrzegorzZ]

Query is slow due to many joins. You will experience even greater slowdown as you add new products, attributes etc.
This module is not optimized properly atm.

[El Patron]

I have found that if you create specific templates that can ease performance issue (I am no expert at this) but if one creates one template....that works for all...then it is going to create needless overhead.  Someday I hope to explore  this more but I think this is issue.