I am failing to optimize this left join ...
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
Re: large tables?
Hi. I find that 1-to-1 relationships are often useful appropriate, and they would help you reduce the number of columns per row. For instance in some db of people, addresses, salary info, medical info, c, --although they could be jammed into one giant row per person, make perfect sense in separate tables with a person-key linking rows in these tables to rows in the person table in a one-to-one relationship. This assumes these 248 piece of data have some logical internal structure that would group them into sets that could describe some entity. My 2 cents. - Brian Hughes Web Developer/Programme Analyst LII, Cornell Law School At 02:52 PM 1/25/2001 +, you wrote: hi, i've been asked to design a for a new web-based system which stores lots of data on it's members. There are currently about 500,000 member records. the problem is that i have to store at least 248 pieces of information on each user. i've made the system as relational as possible so that for each user record, i am only storing integers, for the most part tinyints and smallints. Is there a limit on the number of fields per record. I can easily see this new system requiring 300 fields(columns). what are the consequences for making a table with so many columns. this table will be updated very frequently - will access time degrade severely even though i use mainly ints in this table? thanks for your help. anna - 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 ...
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
