Date: Wed, 13 Aug 2003 11:39:39 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: ProgHome <proghome@silesky.com>
Cc: 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>,
	<pgsql-sql@postgresql.org>
Subject: Re: How to optimize this query ?
In-Reply-To: <001e01c361c8$c55a78a0$0700a8c0@Office3>
Message-ID: <20030813113719.G53899-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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.