Slow query

[CoolTom]

Hi, this query slow down eshop if it have "more" (like 7000+) product. this query execute take about 20-40s. In PHPMyAdmin it say: Copying to tmp table

 

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 200
DAY ) ) >0 AS new, product_shop.price AS orderprice
FROM `smpsp_category_product` cp
LEFT JOIN `smpsp_product` p ON p.`id_product` = cp.`id_product`
INNER JOIN smpsp_product_shop product_shop ON ( product_shop.id_product = p.id_product
AND product_shop.id_shop =2 )
LEFT JOIN `smpsp_product_attribute` pa ON ( p.`id_product` = pa.`id_product` )
LEFT JOIN smpsp_product_attribute_shop product_attribute_shop ON ( product_attribute_shop.id_product_attribute = pa.id_product_attribute
AND product_attribute_shop.id_shop =2
AND product_attribute_shop.`default_on` =1 )
LEFT
JOIN smpsp_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 =2 )
LEFT JOIN `smpsp_category_lang` cl ON ( product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` =2
AND cl.id_shop =2 )
LEFT JOIN `smpsp_product_lang` pl ON ( p.`id_product` = pl.`id_product`
AND pl.`id_lang` =2
AND pl.id_shop =2 )
LEFT JOIN `smpsp_image` i ON ( i.`id_product` = p.`id_product` )
LEFT JOIN smpsp_image_shop image_shop ON ( image_shop.id_image = i.id_image
AND image_shop.id_shop =2
AND image_shop.cover =1 )
LEFT JOIN `smpsp_image_lang` il ON ( image_shop.`id_image` = il.`id_image`
AND il.`id_lang` =2 )
LEFT JOIN `smpsp_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` =2
AND cp.`id_category` =156
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 , 100

 

---------------

 

What can make this long qury??

I can not turn on _PS_DEBUG_PROFILING_ because if i do, it show nothing on frontend :/

 

Thanks for ideas

[musicmaster]

I can remember one case where such a problem was caused because somewhere somehow a database index had disappeared. Re-entering it solved the problem.

 

You seem not to have a copy of your shop on your local computer. Yet that is very much recommended as it allows you to experiment.