Re: How to optimize this query ?

On 13 Aug 2003, Franco Bruno Borghesi wrote:

> Maybe its better now. Anyway, what I think is that joining will perform
> better than using IN. Am I wrong?

Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much
better, and you probably want to retry with IN. However, it's possible
that NOT EXISTS will work better than left joins even in 7.3 and earlier,
I'm not sure, I think it's probably situational.

I think that you're still going to have a problem in the below if there
are purchase rows with member_id 21101 and some other value that both
match. I think you need to do something like the subselect on
affiliate_lockout in the from on purchase as well.

> 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
> ) A ON (L.affiliate_id=A.affiliated_locled_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

[I think this was meant to be member_id from the original query]

> (M.member_id IS NULL) AND
> (A.member_id IS NULL)