From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: ProgHome <proghome@silesky.com>
Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Wed, 13 Aug 2003 11:39:39 -0700 (PDT)
Message-ID: <20030813113719.G53899-100000@megazone.bigpanda.com> (raw)
In-Reply-To: <001e01c361c8$c55a78a0$0700a8c0@Office3>
On Wed, 13 Aug 2003, ProgHome wrote:
> 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))
In these cases I'd suggest trying a NOT EXISTS, maybe something like
(for the first one):
AND NOT EXISTS (select 1 from purchase where member_id=member.id
AND lead_id=$id)
With a two column index on purchase(member_id,lead_id) [or lead_id,
member_id maybe] you might be able to do okay.
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: sszabo@megazone.bigpanda.com, proghome@silesky.com, franco@akyasociados.com.ar
Subject: Re: How to optimize this query ?
In-Reply-To: <20030813113719.G53899-100000@megazone.bigpanda.com>
* 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