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 CCB6CD1B56C for ; Wed, 13 Aug 2003 18:29:25 +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 55151-04 for ; Wed, 13 Aug 2003 15:27:49 -0300 (ADT) Received: from omnet.leaderofthedot.com (unknown [65.210.122.2]) by svr1.postgresql.org (Postfix) with ESMTP id 99588D1B524 for ; Wed, 13 Aug 2003 15:29:14 -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 h7DISnu1016793; Wed, 13 Aug 2003 14:28:49 -0400 (EDT) From: "ProgHome" To: "'Stephan Szabo'" , "'Franco Bruno Borghesi'" Cc: Subject: Re: How to optimize this query ? Date: Wed, 13 Aug 2003 14:29:01 -0400 Message-ID: <001e01c361c8$c55a78a0$0700a8c0@Office3> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001F_01C361A7.3E48D8A0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 In-Reply-To: <20030813093224.V50295-100000@megazone.bigpanda.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Scanned-By: MIMEDefang 2.31 (www . roaringpenguin . com / mimedefang) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/151 X-Sequence-Number: 14767 This is a multi-part message in MIME format. ------=_NextPart_000_001F_01C361A7.3E48D8A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Actually, I have got another query where I need to remove the subqueries ... It is almost the same query but this time, I don't search in the table LEAD but in the table MEMBER . HERE are the tables: affiliate_lockout (6 rows) (member_id, affiliate_id) automated (4 rows) (member_id, .) lead (4490 rows) (id, ...) member (6 rows) (id, ...) member_exclusion (3 rows) (member_id, member_id_to_exclude) purchase (10000 rows) (lead_id, member_id, ...) ---------------------------- select member.id, automated.delivery, member.email from (automated INNER JOIN member ON member.id = automated.member_id) where activated=1 and website='$SITE_NAME' and (select count(*) from trans_member where (unix_timestamp(now())-unix_timestamp(date)) < (unix_timestamp(now())-'$today_midnight') and type='purchase' and comment LIKE '%automated%' and member_id=member.id and comment LIKE '%$type%') < max_$field and balance_in_points > $price and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%' and states LIKE '%$lead[prop_state]%' and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) / cast($lead[current_value] as unsigned)) and amount_t$n < $lead[loan_amount] AND $id NOT IN (select lead_id from purchase where member_id=member.id) AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout where member_id=member.id) AND $id NOT IN (select lead_id from purchase where member_id IN (select member_id_to_exclude from member_exclusion where member_id=member.id)) ORDER BY balance_in_points DESC -------------------- For this one, I really don't know how to remove the 3 subqueries at the end because the $id and $aff_id are values external to the query, and there is no table to join . I tried to remove the subqueries and to rewrite them to 3 small external queries that I run for each result given by this first query, but I guess this will be much longer if the tables are big . What do you think about ? ------=_NextPart_000_001F_01C361A7.3E48D8A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable RE: [SQL] How to optimize this query ?

Ac= tually, I have got another query where I need to remove the subqueries ... It is al= most the same query but this time, I don't search in the table LEAD but in the tabl= e ME= MBER …

HE= RE are the tables:

 = affiliate_lockout  (6 rows)  (member_id, affiliate_id)

&n= bsp;automated      (4 rows)    = ;    (member_id, …)<= /P>

&n= bsp;lead   (4490 rows)       &= nbsp;   (id, ...)

&n= bsp;member  (6 rows)        &n= bsp;    (id, ...)

&n= bsp;member_exclusion (3 rows)    (member_id, member_id_to_ex= clude)

 purchase (10000 rows) &= nbsp;      (lead_id, member_id, ...)


----------------------------

select member.id, automated.delivery, member.email =

 

from (automated INNER JOIN member ON member.id =3D automated.member_id)<= /FONT>

 

where activated=3D1

  and website=3D'$SITE_NAME'

  and (select count(*) from trans_member where (unix_= timestamp(now())-unix_timestamp(date)) < <= FONT COLOR=3D"#000000" SIZE=3D1 FACE=3D"Courier New">(unix_timestamp(now())= -'$today_midnight') and type=3D'purchase' and comment LIKE '%automated%'

  and member_id=3Dmember.id and comment LIKE '%$type%= ') < max_$field

  and balance_in_points > $price

  and credit_ratings_t$n LIKE '%$lead[borrower_credit= _rating]%'

  and states LIKE '%$lead[prop_state]%'

  and ltv_t$n/100 >=3D (cast($lead[loan_amount] as unsigned) / c= ast($lead[current_value] as unsigned))

  and amount_t$n < $lead[loan_amount]

 

  AND $id NOT IN (select lead_id from purchase where = member_id=3Dmember.id)

  AND $aff_id NOT IN (select affiliate_locked_id from= affiliate_lockout where member_id=3Dmember.id)

  AND $id NOT IN (select lead_id from purchase where = member_id IN (select member_id_to_exclude from member_exclusion where membe= r_id=3Dmember.id))

 

ORDER BY balance_in_points DESC

--------------------

 

For this one, I really don’t know how to remove the 3 subqueries at the end beca= use the $id and $aff_id are values external to the query, and there is no t= able to join …

 

I tried to remove the subqueries and to rewrite them to 3 small external= queries that I run for each result given by this first query, but I guess = this will be much longer if the tables are big …

What do you think about ?

------=_NextPart_000_001F_01C361A7.3E48D8A0--