Re: Using SELECT within SELECT in mysql query

[Daniel Le Breton]

hi,

this request give me the good result but it is horrible slow,
(only 1 record regoupement_id : 100, item : 3)

how can I transform this request to get better performance ????




SELECT DISTINCT ri.regroupement_id, ri.item FROM regroupement_item AS ri
INNER JOIN regroupement AS rg1 ON rg1.regroupement_id = ri.regroupement_id
WHERE rg1.regroupement_type = 2

AND ri.item
NOT IN
(
SELECT DISTINCT ri2.item FROM regroupement_item AS ri2
INNER JOIN regroupement AS rg2 ON rg2.regroupement_id = ri2.regroupement_id
WHERE rg2.regroupement_type =1
)

AND ri.regroupement_id
NOT IN
(
SELECT regroupement_id FROM regroupement_item
WHERE item IN (
SELECT ri3.item
FROM regroupement_item AS ri3
INNER JOIN regroupement AS rg3 ON rg3.regroupement_id = ri3.regroupement_id
GROUP BY ri3.item HAVING COUNT(ri3.regroupement_id) > 1
)
)

In advance thank you.