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 like id = 2, affiliate_id = 2 And rows in affiliate_lockout like: affiliate_locked_id=2, member_id=21101 affiliate_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. > -----Original Message----- > From: Stephan Szabo [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 13, 2003 1:10 PM > To: Franco Bruno Borghesi > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [SQL] How to optimize this query ? > > On 13 Aug 2003, Franco Bruno Borghesi wrote: > > > Maybe its better now. Anyway, what I think is that joining will > perform > > better than using IN. Am I wrong? > > Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much > better, and you probably want to retry with IN. However, it's possible > that NOT EXISTS will work better than left joins even in 7.3 and > earlier, > I'm not sure, I think it's probably situational. > > I think that you're still going to have a problem in the below if there > are purchase rows with member_id 21101 and some other value that both > match. I think you need to do something like the subselect on > affiliate_lockout in the from on purchase as well. > > > 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 > > [I think this was meant to be member_id from the original query] > > > (M.member_id IS NULL) AND > > (A.member_id IS NULL) > > > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
