Re: How to optimize this query ?

You were right, Stephan !
The query below is still not correct ... because the second line
shouldn't be shown !
Now I really don't know how I could rewrite this without a subquery
because it doesn't seem to be possible with some LEFT or INNER joins !
Do you have an idea ?

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] 
Sent: Wednesday, August 13, 2003 2:29 PM
To: ProgHome
Cc: 'Franco Bruno Borghesi'; pgsql-sql@postgresql.org
Subject: RE: [SQL] How to optimize this query ?

On Wed, 13 Aug 2003, ProgHome wrote:

> I tried with some LEFT JOINS, which give me the possibility to keep
> the information of the right table.
>
> I have now the following query, which is 10 times faster !!! (from 16s
> to 1.6s)
> But I'd like to remove the last subquery, to see if it faster ;)
>
>
> -------------------------
>
> SELECT lead. *
> FROM lead
> LEFT JOIN purchase ON ( lead.id = purchase.lead_id )
> LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
> affiliate_lockout.affiliate_locked_id )
> WHERE (
> exclusive IS NULL OR (
> exclusive = 0 AND nb_purchases < 3
> )
> ) AND id NOT
> IN (
>
> SELECT lead_id
> FROM purchase
> INNER JOIN member_exclusion
> WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> purchase.member_id = 21101
> ) AND (
> affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
> NULL
> ) AND purchase.member_id <> 21101
> GROUP BY lead.id

As I replied to Franco for his query below, I believe this query is not
equivalent to your original query for a few cases, but those might not
come up.

If you had a row in lead likeid = 2, affiliate_id = 2
And rows in affiliate_lockout like:affiliate_locked_id=2, member_id=21101affiliate_locked_id=2, member_id=31101
should this row in lead be shown or not?

In the original query I think it would not (because lead.affiliate_id
was
IN the affiliate_lockout table where member_id=21101).  In the above
query
I think it will, because one of the joined tables will have the lead
information and a member_id that is not equal to 21101.