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@akyasociados.com.ar]
Sent: Wednesday, August 13, 2003 12:18 PM
To: proghome@silesky.com; pgsql-sql@postgresql.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)