Re: [SQL] How to optimize this query ?
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:[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 4: Don't 'kill -9' the postmaster
Re: [SQL] How to optimize this query ?
Title: RE: [SQL] How to optimize this query ? Actually, I have got another query where I need to remove the subqueries ... It is almost the same query but this time, I don't search in the table LEAD but in the table MEMBER … HERE are the tables: affiliate_lockout (6 rows) (member_id, affiliate_id) automated (4 rows) (member_id, …) lead (4490 rows) (id, ...) member (6 rows) (id, ...) member_exclusion (3 rows) (member_id, member_id_to_exclude) purchase (1 rows) (lead_id, member_id, ...) 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)) ORDER BY balance_in_points DESC For this one, I really don’t know how to remove the 3 subqueries at the end because the $id and $aff_id are values external to the query, and there is no table to join … I tried to remove the subqueries and to rewrite them to 3 small external queries that I run for each result given by this first query, but I guess this will be much longer if the tables are big … What do you think about ?
Re: [SQL] How to optimize this query ?
As I am using mysql 4.0 right now (we’ve got a stupid problem with the 4.1 with the authentification protocol we can’t figure out) and the last subquery (the one in the last LEFT JOIN) MUST be removed … So I tried the following query: SELECT L.* FROM lead L LEFT JOIN purchase P1 ON ( L.id = P1.lead_id ) LEFT JOIN affiliate_lockout A ON ( L.affiliate_id = A.affiliate_locked_id ) LEFT JOIN ( purchase P2 INNER JOIN member_exclusion M ON ( P2.member_id = M.member_id_to_exclude) ) ON ( L.id = P2.lead_id ) WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation ) <= ( 6 * 24 * 3600 ) AND ( exclusive IS NULL OR ( exclusive = 0 AND nb_purchases < 3 ) ) AND ( A.member_id <> 21101 OR A.member_id IS NULL ) AND ( P1.member_id <> 21101 OR P1.member_id IS NULL ) But it seems that the LEFT JOIN doesn’t work anymore and are replaced by OUTER JOIN because the result of the query is (number of rows in Lead * number of rows in PURCHASE * number of rows in …) And it seems that the condition L.id = P2.lead_id doesn’t work either … Could you tell me what the problem is ? Thanks -Original Message- From: Franco Bruno Borghesi [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 12:18 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] 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)
Re: [SQL] 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:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 2:29 PM To: ProgHome Cc: 'Franco Bruno Borghesi'; [EMAIL PROTECTED] 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 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
