Re: How to optimize this query ?

Maybe its better now. Anyway, what I think is that joining will perform
better than using IN. Am I wrong?

SELECT
L.*
FROM
lead L
LEFT JOIN purchase P ON (L.id=P.lead_id)
LEFT JOIN member_exclusion M ON
(P.member_id=M.member_id_to_exclude)
LEFT JOIN (
SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE
member_id=21101
) A ON (L.affiliate_id=A.affiliated_locled_id)
WHERE
L.exclusive IS NULL OR
(
L.exclusive=0 AND
L.nb_purchases<3
) AND
(P.lead_id IS NULL OR P.lead_id<>21101) AND
(M.member_id IS NULL) AND
(A.member_id IS NULL)

On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote:

> On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote:
>
> > I *guess* this query does the same as yours (please verify).
>
> It does not in general unfortunately. :( I see two possible
> problems.
>
> The first is that by using an inner join you're going to lose
> any rows where there is no match on the right hand table which
> the original doesn't.
>
> The second is that if there were two rows in affiliate_lockout
> with the different member_ids but the same affiliate_locked_id
> say, (21101, 10) and (21201, 10) and you were matching a lead row
> with a affiliate_id of 10, the second row would get past the
> condition since it has a member_id!=21101, but the original
> would drop the row because there existed some matching
> affiliate_lockout row where the member_id was 21101.
>
> > SELECT
> > L.*
> > FROM
> > lead L
> > INNER JOIN purchase P ON (L.id=P.lead_id)
> > INNER JOIN affiliate_lockout A ON
> > (L.affiliate_id=A.affiliate_locked_id)
> > INNER JOIN member_exclusion M ON
> > (P.member_id=M.member_id_to_exclude)
> > WHERE
> > L.exclusive IS NULL OR
> > (
> > L.exclusive=0 AND
> > L.nb_purchases<3
> > ) AND
> > P.lead_id<>21101 AND
> > A.member_id<>21011
> >
> > > Hi all
> > >
> > > I have to optmize this query, because it takes a while to run (about
> > > 30s)
> > >
> > > Here are the tables (with the keys):
> > > affiliate_lockout (6 rows) (member_id, affiliate_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, ...)
> > >
> > >
> > > Here is the query:
> > > SELECT *
> > > FROM lead
> > > WHERE
> > > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
> > >
> > > id NOT IN (
> > >
> > > SELECT lead_id
> > > FROM purchase
> > > WHERE member_id = 21101
> > > ) AND affiliate_id NOT
> > > IN (
> > >
> > > SELECT affiliate_locked_id
> > > FROM affiliate_lockout
> > > WHERE member_id = 21101
> > > ) 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
> > > )
> > >
> > >
> > > I wonder the problem is with the subqueries (which are apparently very
> > > slow to run, according to what I read), but I can't figure how to
> > > rewrite this query without any subquery ...
> > >
> > > Maybe the problem comes from the index ... How would you create your
> > > indexes to optimize this query ?
> > >
> > > Could somebody help me ?
> > > Thanks
> > > krystoffff
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 5: Have you checked our
> > > extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>
>
>
>
>