Re: How to optimize this query ?

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.