SQL query optimization in Prestashop e-Commerce solution

Question

I'm working on a layered navigation module within Prestashop e-Commerce solution.

I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a bottleneck in MySQL.

This is the query:

SELECT p.*, p.id_category_default, pl.available_later, pl.description_short,
pl.link_rewrite, pl.name, i.id_image, il.legend, m.name manufacturer_name,
DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 30 DAY)) > 0 AS new 
FROM `ps_category_product` cp 
LEFT JOIN ps_category c ON (c.id_category = cp.id_category) 
LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` 
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product) 
LEFT JOIN ps_image i ON (i.id_product = p.id_product AND i.cover = 1) 
LEFT JOIN ps_image_lang il ON (i.id_image = il.id_image AND il.id_lang = 2) 
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) 
WHERE p.`active` = 1 
    AND c.nleft >= 2 
    AND c.nright <= 29 
    AND c.active = 1 
    AND pl.id_lang = 2 
AND p.id_product IN (74, 78, 130, 146, 168, 169,
178, 195, ..., 297, 302, 1986, 1987, 1988, 1993, 1999, 2000, 2001) 
GROUP BY p.id_product 
ORDER BY p.date_upd desc LIMIT 0,48

I know the temp table is created because of the GROUP BY but I don't know how to remove it. If the same process could be done with PHP instead of SQL, that could be a solution.

Answer 1

SELECT 
    p.id_product, p.id_category_default, pl.available_later, pl.description_short,
    pl.link_rewrite, pl.name, i.id_image, il.legend, m.name AS manufacturer_name,
    DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 30 DAY)) > 0 AS new
FROM `ps_product` p
INNER JOIN `ps_product_lang` pl ON pl.id_product = p.id_product AND pl.id_lang = 2
INNER JOIN `ps_image` i ON i.id_product = p.id_product AND i.cover = 1
INNER JOIN `ps_image_lang` il ON il.id_image = i.id_image AND il.id_lang = 2
INNER JOIN `ps_manufacturer` m ON m.id_manufacturer = p.id_manufacturer
WHERE p.`active` = 1 
    AND p.id_product IN (74, 78, 130, 146, 168, 169, 178, 195, ..., 297, 302, 1986, 1987, 1988, 1993, 1999, 2000, 2001)
ORDER BY p.date_upd DESC
LIMIT 0, 48;