Hello,
I need a query which will select just one (GROUP BY phi.id_product) image for each product and this image have to be the one with the highest priority (inner SELECT with ORDER BY statement).
The priority is stored in N:M relation table called product_has_image
I've created a query, but it tooks about 3 seconds to execute and I need to optimize it. Here it is:
SELECT p.*, i.id AS imageid
FROM `product` p JOIN `category` c on c.`id` = p.`id_category`
LEFT OUTER JOIN (SELECT id_product, id_image FROM
`product_has_image` ORDER BY priority DESC) phi ON p.id = phi.id_product
LEFT OUTER JOIN `image` i ON phi.id_image = i.id
WHERE (c.`id_parent` = 2 OR c.`id` = 2)
GROUP BY phi.id_product
Indexes which I find to be important in this query are:
image (PRIMARY id)
product_has_image (PRIMARY id_product, id_image; INDEX id_product; INDEX id_image)
product (PRIMARY id, id_category; INDEX id_category)
category (PRIMARY id; INDEX id_parent)
Most of the time takes joining the tables using the SELECT statement which is required for sorting.
Joining with LEFT JOIN [product_has_image] phi ON p.id = phi.id_product is much faster, but doesn't assign the image with the highest priority.
Any help would be appreciated.