Hello,
I use a prestashop 1609 and I have around 10,000 products.
I received a message from the hosting company with a recommendation to optimize a mysql query on my site (sql log is in the attachment)
This query consumes a lot of processor time.
In prestashop, this request is located in Product.php at line 2177.
$sql = 'SELECT product_shop.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute
FROM `'._DB_PREFIX_.'product` p
'.Shop::addSqlAssociation('product', 'p').'
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (product_shop.id_product = pa.id_product)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1').'
WHERE product_shop.`active` = 1
'.(($ids_product) ? $ids_product : '').'
AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `'._DB_PREFIX_.'category_group` cg
LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` '.$sql_groups.'
)
'.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').'
GROUP BY product_shop.id_product
ORDER BY RAND()';
Can you recommend a way to optimize?
Thanks!
sqllog.txt