Re: LEFT JOIN function locking up when using large database
Thank you very much! I am a first year software engineer so I'm still learning :-) I had a query that took 23 seconds, and now takes .01 sec. Thanks again! Rob Taft - 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
LEFT JOIN function locking up when using large database
I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll call these table1 and table2. The query uses both tables: SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; table2.table1_ID is just an int that matches an ID from table1. This way I can get several values from 2 tables with one query. This works great when table 1 has 100 entries and table2 has 1,000 entries. But when i increase both by a factor of 10, the query never returns anything. I let it sit for 10 mins before giving up, and my CPU usage is at 100% the whole time. Any suggestions as to what the problem is? I tried this on both 3.23 and 4.0.7 and got the same results. The [EMAIL PROTECTED] would not take my email. Rob Taft [EMAIL PROTECTED] - 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: LEFT JOIN function locking up when using large database
Are you using indexes on your tables? This does not sound like a mysql bug, but rather a problem with your table structure or query. I have seen much more complicated joins work on much larger tables without any problem, as I am sure many people on this list have as well. You should run an explain on your query to make sure it is utilizing indexes. -Original Message- From: Rob Taft [mailto:[EMAIL PROTECTED]] Sent: Monday, January 06, 2003 10:53 AM To: [EMAIL PROTECTED] Subject: LEFT JOIN function locking up when using large database I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll call these table1 and table2. The query uses both tables: SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; table2.table1_ID is just an int that matches an ID from table1. This way I can get several values from 2 tables with one query. This works great when table 1 has 100 entries and table2 has 1,000 entries. But when i increase both by a factor of 10, the query never returns anything. I let it sit for 10 mins before giving up, and my CPU usage is at 100% the whole time. Any suggestions as to what the problem is? I tried this on both 3.23 and 4.0.7 and got the same results. The [EMAIL PROTECTED] would not take my email. Rob Taft [EMAIL PROTECTED] - 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: LEFT JOIN function locking up when using large database
Rob, as Jennifer stated, the problem is that your query doesn't use indexes. SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; some condition would be of interest here. If MySQL can't use indexes here, it will have to scan all of table2 (the big one) to find out which rows match. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Rob Taft [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 06, 2003 7:53 PM Subject: LEFT JOIN function locking up when using large database I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll call these table1 and table2. The query uses both tables: SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; table2.table1_ID is just an int that matches an ID from table1. This way I can get several values from 2 tables with one query. This works great when table 1 has 100 entries and table2 has 1,000 entries. But when i increase both by a factor of 10, the query never returns anything. I let it sit for 10 mins before giving up, and my CPU usage is at 100% the whole time. Any suggestions as to what the problem is? I tried this on both 3.23 and 4.0.7 and got the same results. The [EMAIL PROTECTED] would not take my email. Rob Taft [EMAIL PROTECTED] - 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: LEFT JOIN function locking up when using large database
Rob Taft wrote: I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll call these table1 and table2. The query uses both tables: SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; Do you have indexes on these? alter table2 add index table1_id_idx(table1_ID); -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
