OK, here is the final query without any subquery ...
--------------------------
SELECT L. *
FROM lead L
LEFT JOIN purchase P ON ( L.id = P.lead_id )
LEFT JOIN member_exclusion M ON ( P.member_id = M.member_id_to_exclude
)
LEFT JOIN (
SELECT DISTINCT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AS A ON ( L.affiliate_id = A.affiliate_locked_id )
WHERE L.exclusive IS NULL OR (
L.exclusive = 0 AND L.nb_purchases < 3
) AND (
P.lead_id IS NULL OR P.lead_id <> 21101
) AND (
M.member_id IS NULL
)
GROUP BY L.id
-----------------------
I've got the same result as before, so it should be correct ;)
By the way, the time seems to be the same (1.41s for the last form,
and 1.44s now) but I think it's because I don't have much stuff in
Member_exclusion (6 rows) so I will keep this query without subquery
...
Thanks, Franco !
PS : definitively, try to avoid the subqueries ! It's easy to program,
but very slow to execute !
