From: ProgHome <proghome@silesky.com>
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>
To: 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Wed, 13 Aug 2003 14:29:01 -0400
Message-ID: <001e01c361c8$c55a78a0$0700a8c0@Office3> (raw)
In-Reply-To: <20030813093224.V50295-100000@megazone.bigpanda.com>
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 ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: proghome@silesky.com, sszabo@megazone.bigpanda.com, franco@akyasociados.com.ar
Subject: Re: How to optimize this query ?
In-Reply-To: <001e01c361c8$c55a78a0$0700a8c0@Office3>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox