Re: I am failing to optimize this left join ...
Try this one: SELECT STRAIGHT_JOIN ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date On 25 Jan 2001 13:17:14 -0500, Brian Hughes wrote: Hi. I have a query which works and is quick, but it misses a couple of records. SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Preview.Blurb FROM ArgumentCalendar , CaseID , Parties , Preview WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND Preview.CASEID = CaseID.CASEID AND '20001001' = ArgumentCalendar.Date AND ArgumentCalendar.Date = '20010630; I needed to _also_ select the records which don't have a corresponding record in Preview, so I threw in a left join: SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date The second query works correctly with the left join, but now mysql looks at all 19000 records in Parties (according to EXPLAIN) and the query takes about 15 seconds to execute. I have fiddled and read fiddled more, but I have failed to speed up this query. If I change the order around or add another left join I have removed the problem with the Parties table, but then EXPLAIN says all 19000 CaseID records are being examined. Without the LEFT JOIN MySQL only looks at all the ArgumentCalendar all the Preview records, which are like 63 and 150 respectively. With the Left Join I select 61 records, without it 59 (which is as expected -- the issue is just speed). I am using MySQL 3.22.25. Am I screwed or is there some syntactic SQL point I am missing? - BLH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: I am failing to optimize this left join ...
Does this help? SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Parties.CASEID = Preview.CASEID WHERE Preview.CASEID IS NULL AND CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date Hi. I have a query which works and is quick, but it misses a couple of records. SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Preview.Blurb FROM ArgumentCalendar , CaseID , Parties , Preview WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND Preview.CASEID = CaseID.CASEID AND '20001001' = ArgumentCalendar.Date AND ArgumentCalendar.Date = '20010630; I needed to _also_ select the records which don't have a corresponding record in Preview, so I threw in a left join: SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date The second query works correctly with the left join, but now mysql looks at all 19000 records in Parties (according to EXPLAIN) and the query takes about 15 seconds to execute. I have fiddled and read fiddled more, but I have failed to speed up this query. If I change the order around or add another left join I have removed the problem with the Parties table, but then EXPLAIN says all 19000 CaseID records are being examined. Without the LEFT JOIN MySQL only looks at all the ArgumentCalendar all the Preview records, which are like 63 and 150 respectively. With the Left Join I select 61 records, without it 59 (which is as expected -- the issue is just speed). I am using MySQL 3.22.25. Am I screwed or is there some syntactic SQL point I am missing? - BLH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I am failing to optimize this left join ...
Hi. Thanks, I like that syntax, I didn't know I could put STRAIGHT_JOIN there after the SELECT. But it doesn't get me anything. EXPLAIN still says I am examining all 19,701 rows in CaseID: same problem as before. - BLH At 10:46 AM 1/25/2001 -0800, you wrote: Try this one: SELECT STRAIGHT_JOIN ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date On 25 Jan 2001 13:17:14 -0500, Brian Hughes wrote: Hi. I have a query which works and is quick, but it misses a couple of records. SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Preview.Blurb FROM ArgumentCalendar , CaseID , Parties , Preview WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND Preview.CASEID = CaseID.CASEID AND '20001001' = ArgumentCalendar.Date AND ArgumentCalendar.Date = '20010630; I needed to _also_ select the records which don't have a corresponding record in Preview, so I threw in a left join: SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date The second query works correctly with the left join, but now mysql looks at all 19000 records in Parties (according to EXPLAIN) and the query takes about 15 seconds to execute. I have fiddled and read fiddled more, but I have failed to speed up this query. If I change the order around or add another left join I have removed the problem with the Parties table, but then EXPLAIN says all 19000 CaseID records are being examined. Without the LEFT JOIN MySQL only looks at all the ArgumentCalendar all the Preview records, which are like 63 and 150 respectively. With the Left Join I select 61 records, without it 59 (which is as expected -- the issue is just speed). I am using MySQL 3.22.25. Am I screwed or is there some syntactic SQL point I am missing? - BLH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: I am failing to optimize this left join ...
Thanks, but not really. I wish to get all the cases, even when there is no Blurb in Preview. Your query only grabs cases where the Blurb in Preview is missing by adding WHERE Preview.CASEID IS NULL. - Brian - BLH At 01:00 PM 1/25/2001 -0600, you wrote: Does this help? SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Parties.CASEID = Preview.CASEID WHERE Preview.CASEID IS NULL AND CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date Hi. I have a query which works and is quick, but it misses a couple of records. SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Preview.Blurb FROM ArgumentCalendar , CaseID , Parties , Preview WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND Preview.CASEID = CaseID.CASEID AND '20001001' = ArgumentCalendar.Date AND ArgumentCalendar.Date = '20010630; I needed to _also_ select the records which don't have a corresponding record in Preview, so I threw in a left join: SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date The second query works correctly with the left join, but now mysql looks at all 19000 records in Parties (according to EXPLAIN) and the query takes about 15 seconds to execute. I have fiddled and read fiddled more, but I have failed to speed up this query. If I change the order around or add another left join I have removed the problem with the Parties table, but then EXPLAIN says all 19000 CaseID records are being examined. Without the LEFT JOIN MySQL only looks at all the ArgumentCalendar all the Preview records, which are like 63 and 150 respectively. With the Left Join I select 61 records, without it 59 (which is as expected -- the issue is just speed). I am using MySQL 3.22.25. Am I screwed or is there some syntactic SQL point I am missing? - BLH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I am failing to optimize this left join ...
Send the results of the EXPLAIN On 25 Jan 2001 15:15:35 -0500, Brian Hughes wrote: Hi. Thanks, I like that syntax, I didn't know I could put STRAIGHT_JOIN there after the SELECT. But it doesn't get me anything. EXPLAIN still says I am examining all 19,701 rows in CaseID: same problem as before. - BLH At 10:46 AM 1/25/2001 -0800, you wrote: Try this one: SELECT STRAIGHT_JOIN ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date On 25 Jan 2001 13:17:14 -0500, Brian Hughes wrote: Hi. I have a query which works and is quick, but it misses a couple of records. SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Preview.Blurb FROM ArgumentCalendar , CaseID , Parties , Preview WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND Preview.CASEID = CaseID.CASEID AND '20001001' = ArgumentCalendar.Date AND ArgumentCalendar.Date = '20010630; I needed to _also_ select the records which don't have a corresponding record in Preview, so I threw in a left join: SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date The second query works correctly with the left join, but now mysql looks at all 19000 records in Parties (according to EXPLAIN) and the query takes about 15 seconds to execute. I have fiddled and read fiddled more, but I have failed to speed up this query. If I change the order around or add another left join I have removed the problem with the Parties table, but then EXPLAIN says all 19000 CaseID records are being examined. Without the LEFT JOIN MySQL only looks at all the ArgumentCalendar all the Preview records, which are like 63 and 150 respectively. With the Left Join I select 61 records, without it 59 (which is as expected -- the issue is just speed). I am using MySQL 3.22.25. Am I screwed or is there some syntactic SQL point I am missing? - BLH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I am failing to optimize this left join ...
At 01:19 PM 1/25/2001 -0800, Ryan Wahle wrote: Send the results of the EXPLAIN table type possible_keys key key_len ref rows Extra ArgumentCalendar ALL Date [none] [none] [none] 63 where used CaseID ALL PRIMARY [none] [none] [none] 19701 where used Parties eq_ref PRIMARY PRIMARY 4 CaseID.CASEID 1 [none] Preview ALL [none] [none] [none] [none] 150 [none] (blank cells indicated by [none]) - BLH On 25 Jan 2001 15:15:35 -0500, Brian Hughes wrote: Hi. Thanks, I like that syntax, I didn't know I could put STRAIGHT_JOIN there after the SELECT. But it doesn't get me anything. EXPLAIN still says I am examining all 19,701 rows in CaseID: same problem as before. - BLH At 10:46 AM 1/25/2001 -0800, you wrote: Try this one: SELECT STRAIGHT_JOIN ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date On 25 Jan 2001 13:17:14 -0500, Brian Hughes wrote: Hi. I have a query which works and is quick, but it misses a couple of records. SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Preview.Blurb FROM ArgumentCalendar , CaseID , Parties , Preview WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND Preview.CASEID = CaseID.CASEID AND '20001001' = ArgumentCalendar.Date AND ArgumentCalendar.Date = '20010630; I needed to _also_ select the records which don't have a corresponding record in Preview, so I threw in a left join: SELECT ArgumentCalendar.Docket , ArgumentCalendar.Date , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date , CaseID.CASEID , CONCAT(Party1, ' v. ', Party2) AS name , Blurb FROM ArgumentCalendar , CaseID , Parties LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID WHERE CaseID.DocketNumber = ArgumentCalendar.Docket AND Parties.CASEID = CaseID.CASEID AND '20010125' = ArgumentCalendar.Date The second query works correctly with the left join, but now mysql looks at all 19000 records in Parties (according to EXPLAIN) and the query takes about 15 seconds to execute. I have fiddled and read fiddled more, but I have failed to speed up this query. If I change the order around or add another left join I have removed the problem with the Parties table, but then EXPLAIN says all 19000 CaseID records are being examined. Without the LEFT JOIN MySQL only looks at all the ArgumentCalendar all the Preview records, which are like 63 and 150 respectively. With the Left Join I select 61 records, without it 59 (which is as expected -- the issue is just speed). I am using MySQL 3.22.25. Am I screwed or is there some syntactic SQL point I am missing? - BLH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
