Edit History

MySQL on our server was updated from version 5.5 to 5.6 last weekend and that made our website incredibly slow.
With MySQL 5.5 our main category page loaded in about 2-3 sec.
But now, running MySQL 5.6, the same page takes about 16-20 sec.

We activated "Slow Queries" and found that the problem is caused by blocklayered. More precisely the query that handles attribute groups.
If we disabled that query, the page loads again in 2-3 sec.

For the time being, I adjusted the filters in blocklayered, so that the attribute groups are not used as a filter. That has the same effect as disabling the query in the source.

This is the query I am talking about:
 

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, 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 = 7
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 = 7
LEFT JOIN ps_layered_indexable_attribute_group_lang_value liagl
ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = 7)
LEFT JOIN ps_layered_indexable_attribute_lang_value lial
ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = 7)  
INNER JOIN ps_product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
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 >= 4
		AND c.nright <= 5
		AND c.active = 1)
	) 
GROUP BY lpa.id_attribute
ORDER BY ag.`position` ASC, a.`position` ASC;

If I run this query in PhpMyAdmin on our server it loads incredibly slow (15.55 sec for only 84 results).
Running the same query on our test server with MySQL 5.5 only takes 0.43 sec!!!

So now the main question:
What in this query can cause such a huge difference in loadtimes?

Thanks for any help.