Re: How to optimize this query ?

As I am using mysql 4.0 right now (we've got a stupid problem with the
4.1 with the authentification protocol we can't figure out) and the last
subquery (the one in the last LEFT JOIN) MUST be removed .

So I tried the following query:

SELECT
L.*
FROM lead L
LEFT JOIN purchase P1 ON ( L.id = P1.lead_id )
LEFT JOIN affiliate_lockout A ON ( L.affiliate_id =
A.affiliate_locked_id )
LEFT JOIN (
purchase P2
INNER JOIN member_exclusion M ON ( P2.member_id =
M.member_id_to_exclude)
) ON ( L.id = P2.lead_id )
WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation ) <= ( 6
* 24 * 3600 ) AND (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND (
A.member_id <> 21101 OR A.member_id IS NULL )
AND ( P1.member_id <> 21101 OR P1.member_id IS NULL )

But it seems that the LEFT JOIN doesn't work anymore and are replaced by
OUTER JOIN because the result of the query is (number of rows in Lead *
number of rows in PURCHASE * number of rows in .)
And it seems that the condition L.id = P2.lead_id doesn't work either .

Could you tell me what the problem is ?
Thanks


-----Original Message-----
From: Franco Bruno Borghesi [mailto:franco(at)akyasociados(dot)com(dot)ar]
Sent: Wednesday, August 13, 2003 12:18 PM
To: proghome(at)silesky(dot)com; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] How to optimize this query ?

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

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

(M.member_id IS NULL) AND

(A.member_id IS NULL)