Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.