Обсуждение: How to optimize this query ?
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 (10000rows) (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
On 12 Aug 2003, krystoffff wrote: > 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, ...) > I think you can possibly get better results in 7.3 and earlier by using NOT EXISTS. > Here is the query: > SELECT * > FROM lead > WHERE > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND > NOT EXISTS (select lead_id from purchase where lead_id=lead.id and member_id=21101) > id NOT IN ( > SELECT lead_id > FROM purchase > WHERE member_id = 21101 AND NOT EXISTS (select * from affiliate_lockoutWHERE member_id=21101 and affiliate_locked_id=lead.affiliate_id) > ) 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 Doesn't this condition end up giving you a subset of the rows in the first one?
I *guess* this query does the same as yours (please verify). SELECTL.* FROMlead LINNER 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) WHEREL.exclusive IS NULL OR( L.exclusive=0 AND L.nb_purchases<3) ANDP.lead_id<>21101 ANDA.member_id<>21011 Hope it performs better. > 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
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 >
Maybe its better now. Anyway, what I think is that joining will perform better than using IN. Am I wrong?<br /><br /> SELECT<br/> L.*<br /> FROM<br /> lead L<br /> LEFT JOIN purchase P ON (L.id=P.lead_id)<br /> LEFT JOINmember_exclusion M ON (P.member_id=M.member_id_to_exclude)<br /> LEFT JOIN (<br /> SELECT DISTINCT affiliate_locked_idFROM affiliate_lockout WHERE member_id=21101<br /> ) A ON (L.affiliate_id=A.affiliated_locled_id)<br/> WHERE<br /> L.exclusive IS NULL OR<br /> (<br /> L.exclusive=0 AND<br /> L.nb_purchases<3<br /> ) AND<br /> (P.lead_id ISNULL OR P.lead_id<>21101) AND<br /> (M.member_id IS NULL) AND<br /> (A.member_id IS NULL)<br /><br /><br/> On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote: <blockquote type="CITE"><pre><font color="#737373"><i>On Tue, 12Aug 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? > > > > </i></font><a href="http://www.postgresql.org/docs/faqs/FAQ.html"><u>http://www.postgresql.org/docs/faqs/FAQ.html</u></a> <font color="#737373">> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > </font> </pre></blockquote>
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)
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. > -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] > Sent: Wednesday, August 13, 2003 1:10 PM > To: Franco Bruno Borghesi > Cc: proghome@silesky.com; pgsql-sql@postgresql.org > 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) > > > > >
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.idAND 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.
OK, here is the final query without any subquery ... -------------------------- 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 ) AS A ON ( L.affiliate_id = A.affiliate_locked_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 ) GROUP BY L.id ----------------------- I've got the same result as before, so it should be correct ;) By the way, the time seems to be the same (1.41s for the last form, and 1.44s now) but I think it's because I don't have much stuff in Member_exclusion (6 rows) so I will keep this query without subquery ... Thanks, Franco ! PS : definitively, try to avoid the subqueries ! It's easy to program, but very slow to execute !
<p align="LEFT"><span lang="en-us"><font face="Courier New" size="2">Actually,</font></span><span lang="en-us"> <font face="CourierNew" size="2">I have got another query where I need to remove the subqueries ...</font></span><span lang="en-us"><fontface="Courier New" size="2"> It is almost the same query but this time, I don't search in the table LEA</font></span><spanlang="en-us"><font face="Courier New" size="2">D</font></span><span lang="en-us"><font face="CourierNew" size="2"> but in the table</font></span><span lang="en-us"> <font face="Courier New" size="2">MEMBER</font></span><spanlang="en-us"> <font face="Courier New" size="2">…</font></span><span lang="en-us"></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2">HERE are the tables:</font></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2"></font></span><span lang="en-us"> <fontface="Courier New" size="2">affiliate_lockout (6 rows) (member_id, affiliate_id)</font></span><spanlang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Courier New" size="2"> automated (4 rows) (member_id,</font></span><span lang="en-us"> <font face="Courier New" size="2">…</font></span><spanlang="en-us"><font face="Courier New" size="2">)</font></span><span lang="en-us"></span><p align="LEFT"><spanlang="en-us"><font face="Courier New" size="2"> lead (4490 rows) (id, ...)</font></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2"> member (6 rows) (id,...)</font></span><p align="LEFT"><span lang="en-us"><font face="Courier New" size="2"> member_exclusion (3 rows) (member_id, member_id_to_exclude)</font></span><p align="LEFT"><span lang="en-us"><font face="Courier New" size="2"></font> <fontface="Courier New" size="2">purchase (10000 rows) (lead_id, member_id, ...</font><font face="CourierNew" size="2">)</font></span><br /><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New"size="1">----------------------------</font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="CourierNew" size="1">select member.id, automated.delivery, member.email </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"face="Courier New" size="1">from (automated INNER JOIN member ON</font> <font color="#000000" face="CourierNew" size="1">member.id = automated.member_id)</font></span><p align="LEFT"><span lang="en-us"><font color="#000000"face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="CourierNew" size="1">where activated=1</font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="CourierNew" size="1"> and website='$SITE_NAME'</font></span><p align="LEFT"><span lang="en-us"><font color="#000000"face="Courier New" size="1"> and (select count(*) from trans_member where (unix_timestamp(now())-unix_timestamp(date))<</font></span><span lang="en-us"></span><span lang="en-us"><font color="#000000"face="Courier New" size="1"></font></span><span lang="en-us"></span><span lang="en-us"> <font color="#000000"face="Courier New" size="1">(unix_timestamp(now())-'$today_midnight') and type='purchase' and comment LIKE</font><fontcolor="#000000" face="Courier New" size="1"> '%automated%' </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> and member_id=member.id and comment LIKE '%$type%') <max_$field </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1"> and balance_in_points> $price </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1"> and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%' </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> and states LIKE '%$lead[prop_state]%' </font></span><p align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="1"> and ltv_t$n/100 >= (cast(</font><fontcolor="#000000" face="Courier New" size="1">$lead[loan_amount] as unsigned) / cast($lead[current_value]as unsigned)) </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="CourierNew" size="1"> and amount_t$n < $lead[loan_amount] </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"face="Courier New" size="1"> AND $id NOT IN (select lead_id from purchase where member_id=member.id) </font></span><palign="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1"> AND $aff_id NOT IN (selectaffiliate_locked_id from affiliate_lockout where member_id=member.id) </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> AND $id NOT IN (select lead_id from purchase where member_idIN (select member_id_to_exclude from member_exclusion where member_id=member.id))</font></span><p align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="1">ORDER</font> <font color="#000000" face="Courier New" size="1">BYbalance_in_points DESC</font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New"size="2">--------------------</font></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><font color="#000000"face="Courier New" size="2"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="CourierNew" size="2">For this one, I really don</font><font color="#000000" face="Courier New" size="2">’</font><fontcolor="#000000" face="Courier New" size="2">t know how to remove the 3 subqueries at the end becausethe $id and $aff_id are values external to the query, and there is no table to join …</font></span><p align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="2"> </font></span><p align="LEFT"><span lang="en-us"><fontcolor="#000000" face="Courier New" size="2">I tried to remove the subqueries and to rewrite them</font><fontcolor="#000000" face="Courier New" size="2"> to 3 small external queries that I run for each result givenby this first query, but I guess this will be much longer if the tables are big …</font></span><span lang="en-us"></span><palign="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="2">What do you thinkabout ?</font></span><span lang="en-us"></span>
Sorry, I posted the following message on the newsgroups, but it seems that you didn't see it ... 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 -----Original Message----- From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] Sent: Wednesday, August 13, 2003 1:10 PM To: Franco Bruno Borghesi Cc: proghome@silesky.com; pgsql-sql@postgresql.org 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)
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's like to remove the last subquery, to see if it faster ;) Can somebody help me ? ------------------------- 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
For the following query, I have a little problem ... First, I have to rewrite several times the same query because the alias are not recognised in the same query ( I got an error when I try to reuse the alias "nb_bogus_leads", for instance). Do you have a way to avoid this ? Because If I do so, the same query is calculated twice ... Second problem, the most important : The A.id should be for each result returned in A.*, and there should be a join to calculate the query "nb_bogus_leads" (for instance) about the A.id currently processed by the query. But it seems that this join doesn't work, because I have the same "nb_bogus_leads" and same "nb_leads_submitted" for each A.id returned (they should be different !) How can you make this query work ? Thanks SELECT A. * , ( SELECT CAST( count( * ) AS UNSIGNED ) FROM request INNER JOIN lead ON ( lead_id = lead.id ) WHERE allowed = 1 AND lead.affiliate_id = A.id ) AS nb_bogus_leads, ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) AS nb_leads_submitted, ( CASE WHEN ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) <> 0 THEN ( SELECT CAST( count( * ) AS UNSIGNED ) FROM request INNER JOIN lead ON ( lead_id = lead.id ) WHERE allowed = 1 AND lead.affiliate_id = A.id ) / ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) * 100 WHEN ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) = 0 THEN 0 END ) AS percentage_bogus_leads FROM affiliate A WHERE website = 'dev'
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">As I am using <span class="SpellE">mysql</span> 4.0 right now (we’ve got a stupid problemwith the 4.1 with the <span class="SpellE">authentification</span> protocol we can’t figure out) and the last <spanclass="SpellE">subquery</span> (the one in the last LEFT JOIN) MUST be removed …</span></font><p class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">So I tried the following query:</span></font><p class="MsoNormal"><span class="syntaxalphasyntaxalphareservedword"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt"> </span></font></span><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">SELECT<br /> <span class="GramE">L.*</span><span class="syntaxalphasyntaxalphareservedword"></span></span></font><pclass="MsoNormal"><span class="syntaxalphasyntaxalphareservedword"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">FROM</span></font></span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">lead</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">L</span><br/><span class="syntaxalphasyntaxalphareservedword">LEFT</span><span class="syntax"></span><span class="syntaxalphasyntaxalphareservedword">JOIN</span><span class="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">purchase</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P1</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">ON</span><spanclass="syntax"> </span><span class="GramE"><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">id</span></span></span><spanclass="syntax"> </span><span class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P1</span><span class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">lead_id</span><span class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><br /><spanclass="syntaxalphasyntaxalphareservedword">LEFT</span><span class="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">JOIN</span><spanclass="syntax"> </span><span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">affiliate_lockout</span></span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">A</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">ON</span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">affiliate_id</span></span><spanclass="syntax"> </span><span class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">A</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">affiliate_locked_id</span></span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><spanclass="syntax"> </span><br /><span class="syntaxalphasyntaxalphareservedword">LEFT</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">JOIN</span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><p class="MsoNormal"><span class="GramE"><spanclass="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><span style="font-size:12.0pt">purchase</span></font></span></span><span class="syntaxalphasyntaxalphaidentifier">P2</span><br/><span class="syntaxalphasyntaxalphareservedword">INNER</span> <spanclass="syntaxalphasyntaxalphareservedword">JOIN</span> <span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">member_exclusion</span></span><span class="syntaxalphasyntaxalphaidentifier">M</span><span class="syntaxalphasyntaxalphareservedword">ON</span> <span class="GramE"><spanclass="syntaxpunctsyntaxpunctbracketopenround">(</span> <span class="syntaxalphasyntaxalphaidentifier">P2</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">member</span></span><spanclass="syntaxalphasyntaxalphaidentifier">_id</span> <spanclass="syntaxpunct">=</span> <span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">M</span><span class="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">member_id_to_exclude</span></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><pclass="MsoNormal"><span class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">)</span></font></span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">ON</span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">id</span></span><spanclass="syntax"> </span><span class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P2</span><span class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">lead_id</span><span class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><br /><spanclass="syntaxalphasyntaxalphareservedword">WHERE</span><span class="syntax"> </span><span class="syntaxalphasyntaxalphafunctionname">UNIX_TIMESTAMP</span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphafunctionname">now</span><spanclass="syntaxpunctsyntaxpunctbracketopenround">(</span><span class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><spanclass="syntax"> </span><span class="syntaxpunct">-</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphafunctionname">UNIX_TIMESTAMP</span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">date_creation</span></span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><spanclass="syntax"> </span><span class="syntaxpunct"><=</span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="syntaxdigitsyntaxdigitinteger">6</span><spanclass="syntax"> </span><span class="syntaxpunct">*</span><span class="syntax"></span><span class="syntaxdigitsyntaxdigitinteger">24</span><span class="syntax"> </span><span class="syntaxpunct">*</span><spanclass="syntax"> </span><span class="syntaxdigitsyntaxdigitinteger">3600</span><span class="syntax"></span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><span class="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">AND</span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><p class="MsoNormal"><span class="GramE"><spanclass="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><span style="font-size:12.0pt">exclusive</span></font></span></span><span class="syntaxalphasyntaxalphareservedword">IS</span><span class="syntaxalphasyntaxalphareservedword">NULL</span> <span class="syntaxalphasyntaxalphareservedword">OR</span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><p class="MsoNormal"><spanclass="GramE"><span class="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><spanstyle="font-size:12.0pt">exclusive</span></font></span></span> <span class="syntaxpunct">=</span> <span class="syntaxdigitsyntaxdigitinteger">0</span><span class="syntaxalphasyntaxalphareservedword">AND</span> <span class="SpellE"><spanclass="syntaxalphasyntaxalphaidentifier">nb_purchases</span></span> <span class="syntaxpunct"><</span><span class="syntaxdigitsyntaxdigitinteger">3</span><p class="MsoNormal"><span class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">)</span></font></span><pclass="MsoNormal"><span class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">)</span></font></span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphareservedword">AND</span><spanclass="syntax"> </span><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><p class="MsoNormal"><span class="SpellE"><spanclass="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman" size="3"><span style="font-size:12.0pt">A.member_id</span></font></span></span><span class="syntaxpunct"><></span> <span class="syntaxdigitsyntaxdigitinteger">21101</span><span class="syntaxalphasyntaxalphareservedword">OR</span> <span class="SpellE"><spanclass="syntaxalphasyntaxalphaidentifier">A</span><span class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">member_id</span></span> <spanclass="syntaxalphasyntaxalphareservedword">IS</span> <span class="GramE"><span class="syntaxalphasyntaxalphareservedword">NULL</span><span class="syntaxpunctsyntaxpunctbracketcloseround">)</span></span><spanclass="syntax"> </span><p class="MsoNormal"><span class="syntaxalphasyntaxalphareservedword"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">AND</span></font></span><spanclass="syntax"> </span><span class="GramE"><span class="syntaxpunctsyntaxpunctbracketopenround">(</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P1</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">member</span></span><spanclass="syntaxalphasyntaxalphaidentifier">_id</span> <spanclass="syntaxpunct"><></span> <span class="syntaxdigitsyntaxdigitinteger">21101</span> <span class="syntaxalphasyntaxalphareservedword">OR</span><span class="syntaxalphasyntaxalphaidentifier">P1</span><span class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">member_id</span> <span class="syntaxalphasyntaxalphareservedword">IS</span><span class="syntaxalphasyntaxalphareservedword">NULL</span> <span class="syntaxpunctsyntaxpunctbracketcloseround">)</span><pclass="MsoNormal"><span class="syntaxpunctsyntaxpunctbracketcloseround"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt"> </span></font></span><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">But it seems that the LEFT JOIN doesn’t work anymore and are replaced by OUTER JOIN because the result of the queryis (number of rows in Lead * number of rows in PURCHASE * number of rows in …)</span></font><p class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 12.0pt">And it seems that the condition <span class="SpellE"><span class="syntaxalphasyntaxalphaidentifier">L</span><spanclass="syntaxpunctsyntaxpunctqualifier">.</span><span class="syntaxalphasyntaxalphaidentifier">id</span></span><spanclass="syntax"> </span><span class="syntaxpunct">=</span><spanclass="syntax"> </span><span class="syntaxalphasyntaxalphaidentifier">P2</span><span class="syntaxpunctsyntaxpunctqualifier">.</span><spanclass="syntaxalphasyntaxalphaidentifier">lead_id doesn’t work either…</span></span></font><p class="MsoNormal"><span class="syntaxalphasyntaxalphaidentifier"><font face="Times New Roman"size="3"><span style="font-size:12.0pt"> </span></font></span><p class="MsoNormal"><span class="syntaxalphasyntaxalphaidentifier"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">Could you tellme what the problem <span class="GramE">is ?</span></span></font></span><p class="MsoNormal"><span class="syntaxalphasyntaxalphaidentifier"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt">Thanks</span></font></span><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal" style="margin-left:.5in"><font face="Tahoma" size="2"><spanstyle="font-size:10.0pt;font-family:Tahoma">-----Original Message-----<br /><b><span style="font-weight:bold">From:</span></b>Franco Bruno Borghesi [mailto:franco@akyasociados.com.ar] <br /><b><span style="font-weight:bold">Sent:</span></b></span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">Wednesday,August 13, 2003</span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma"></span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">12:18PM</span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma"><br/><b><span style="font-weight:bold">To:</span></b> proghome@silesky.com; pgsql-sql@postgresql.org<br/><b><span style="font-weight:bold">Subject:</span></b> Re: [SQL] How to optimize this query ?</span></font><pclass="MsoNormal" style="margin-left:.5in"><font face="Times New Roman" size="3"><span style="font-size:12.0pt"> </span></font><prestyle="margin-left:.5in"><font face="Courier New" size="2"><span style="font-size:10.0pt">Maybeits better now. Anyway, what I think is that joining will perform better than using IN. AmI wrong?<br /> <br /> SELECT<br /> <span class="GramE">L.</span>*<br /> FROM<br /> lead L<br /> LEFT JOIN purchase P ON (L.id=P.lead_id)<br /> LEFT JOIN member_exclusion M ON (P.member_id=M.member_id_to_exclude)<br /> LEFT JOIN (<br /> SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE member_id=21101<br /> ) A ON (L.affiliate_id=A.affiliated_locled_id)<br /> WHERE<br /> L.exclusive IS NULL OR<br /> (<br /> L.exclusive=0 AND<br /> L.nb_purchases<3<br /> ) AND<br /> (P.lead_id IS NULL OR P.lead_id<>21101) AND<br /> (M.member_id IS NULL) AND<br /> (A.member_id IS NULL)<br /> <i><font color="#737373"><span style="color:#737373;font-style:italic"><span style="mso-spacerun:yes"> </span></span></font></i></span></font></pre><prestyle="margin-left:.5in"><font face="Courier New"size="2"><span style="font-size:10.0pt"> </span></font></pre></div>
On Wed, 27 Aug 2003, ProgHome wrote: > 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 ? The only ways I can think of are through a subquery (*) or possibly there might be a way to do it with EXCEPT, but on first blush that seems like it might be difficult and probably not any better speed wise. (*) - You were using IN (subquery) which is known to be poorly optimized for 7.3 and earlier. You might want to see how it performs on your data under 7.4beta for forward looking, and/or consider converting into a form using EXISTS rather than IN.
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.