X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 03FFAD1B8BE for ; Fri, 15 Aug 2003 20:26:01 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 28478-05 for ; Fri, 15 Aug 2003 17:25:51 -0300 (ADT) Received: from omnet.leaderofthedot.com (unknown [65.210.122.2]) by svr1.postgresql.org (Postfix) with ESMTP id 84524D1B8BC for ; Fri, 15 Aug 2003 17:25:49 -0300 (ADT) Received: from Office3 (pcp01840738pcs.owngsm01.md.comcast.net [68.32.121.151]) by omnet.leaderofthedot.com (8.12.9/8.12.9) with SMTP id h7FKOB1m007636; Fri, 15 Aug 2003 16:24:11 -0400 (EDT) From: "ProgHome" To: "'Franco Bruno Borghesi'" , Subject: Re: How to optimize this query ? Date: Fri, 15 Aug 2003 16:24:24 -0400 Message-ID: <005801c3636b$37ef0e50$0700a8c0@Office3> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0059_01C36349.B0DD6E50" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 In-Reply-To: <1060791455.1082.1.camel@taz.oficina> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Scanned-By: MIMEDefang 2.36 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/197 X-Sequence-Number: 14813 This is a multi-part message in MIME format. ------=_NextPart_000_0059_01C36349.B0DD6E50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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) ------=_NextPart_000_0059_01C36349.B0DD6E50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

As I am using mys= ql 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<= /span> L
LEFT JOIN= purchase P1 ON <= /span>(= L.id =3D P1.lead_id )
LEFT JOIN= affiliate_loc= kout A ON<= /span> ( L.affiliate_id =3D A.affiliate_locked_id<= span class=3Dsyntax> )
LEFT JOIN= (

purchase P2
INNER JOIN member_exclusion M ON ( P2.member_id =3D M.member_id_to_exclude= )

= )= ON<= /span> ( L.id =3D P2.lead_id )
WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation= ) <=3D ( 6 = * 24 * 3600 ) AND = (

exclusive IS NULL OR (

exclusive =3D = 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 )

= &nb= sp;

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 =3D 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 op= timize this query ?

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



SELECT

   L.*

FROM

      lead L

      LEFT JOIN purchase P ON (L.id=3DP.lead_id)
      LEFT JOIN member_exclusion M ON (P.member_id=
=3DM.member_id_to_exclude)

      LEFT JOIN (

       SELECT DISTINCT affiliate_locked_id FROM af=
filiate_lockout WHERE member_id=3D21101

    ) A ON (L.affiliate_id=3DA.affiliated_locled_id)

WHERE

        L.exclusive IS NULL OR

        (

            &nb=
sp;   L.exclusive=3D0 AND

            &nb=
sp;   L.nb_purchases<3

        ) AND

        (P.lead_id IS NULL OR P.lead_id&=
lt;>21101) AND

  (M.member_id IS NULL) AND

        (A.member_id IS NULL)

=
 =

------=_NextPart_000_0059_01C36349.B0DD6E50--