very long MYSQL takes very long in product category

[spearmootz]

sometimes when i am browsing in my shop, randomely i get these really long queries that take 40 seconds.  i tried with a different browsers to make sure it wasnt something with regards to cache, i tried all the performance settings.

 

this happens in the product category only. this goes on until id_product > 16000 or so which is how many products i have in the whole store.

 

SELECT SQL_NO_CACHE product_shop.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute
FROM `ps_product` p
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 (product_shop.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)
WHERE product_shop.`active` = 1
AND (( product_shop.`id_product` = 1) OR( product_shop.`id_product` = 2) OR( product_shop.`id_product` = 3) OR( product_shop.`id_product` = 4) OR( product_shop.`id_product` = 5) OR( product_shop.`id_product` = 6) OR( product_shop.`id_product` = 7) OR( product_shop.`id_product` = 8) OR( product_shop.`id_product` = 9) OR( product_shop.`id_product` = 10) OR( product_shop.`id_product` = 11) OR( product_shop.`id_product` = 12) OR( product_shop.`id_product` = 13) OR( product_shop.`id_product` = 14) OR( product_shop.`id_product` = 15) OR( product_shop.`id_product` = 16) OR( product_shop.`id_product` = 17) OR( product_shop.`id_product` = 18) OR( product_shop.`id_product` = 19) OR( product_shop.`id_product` = 20) OR( product_shop.`id_product` = 21) OR( product_shop.`id_product` = 22)

[NemoPS]

Seems to be coming from the layered navigation, can you disable it and see if it really is?

[spearmootz]

i should have mentioned it.  the layered navigation was disabled when i got that query.

[spearmootz]

ok, so i just noticed that you can look at what is creating the query by looking at the right column, i noticed that the 30-40 second long query was being created by the specials block module, i disabled it and now i am left with a 1 second query created by the layered block which is not too bad. there is a programming issue to be looked at in those two modules.  if you look at it mysql has to make thousands of comparisons just to evaluate one row in mysql so there is this exponential effect.

[NemoPS]

Oh, that's interesting, I didn't know the specials block had this kind of issue, I was only aware of the layered one.