Re: [SQL] How to optimize this query ?

[ProgHome]

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:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 12:18 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 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)