Joins are damn slow. . .
Maybe I'm a bit naive in saying this but. . . SQL joins are damn slow! I have a simple database of about 37,000 records in [Court Cases] and 2,000,000 records in [Defendants] (soon to be more). When I do a search on [Defendants] (the big table) alone, a saerch can go in like .7 seconds. But if I do a search on [Defendants] joined to [Cases], the search jumps to about 5 seconds. (yes, I indexed the joining fields and the search terms). This bites. . . However. I notice that if I do two separate searches it goes quicker (about 2.5 seconds combined). I can do a criteria search on defendants and then put all the resulting case numbers in a temporary table. Then do a join of that temporary table to the much smaller Cases table and do a search on that. I get the same results, and the query time is halved. Ummm, is there any reason why I shouldn't do this? (Other than the inelegance of running two queries instaed of one) Do people do stuff like this for performance reasons? - Steve - 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: Joins are damn slow. . .
Steve, SQL joins are damn slow! I have a simple database of about 37,000 records in [Court Cases] and 2,000,000 records in [Defendants] (soon to be more). Please post the structures of your 2 tables (at least, the relevant parts), and the join query that gets so slow. Otherwise, we can only guess. 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: Steve Quezadas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 10:30 AM Subject: Joins are damn slow. . . Maybe I'm a bit naive in saying this but. . . SQL joins are damn slow! I have a simple database of about 37,000 records in [Court Cases] and 2,000,000 records in [Defendants] (soon to be more). When I do a search on [Defendants] (the big table) alone, a saerch can go in like .7 seconds. But if I do a search on [Defendants] joined to [Cases], the search jumps to about 5 seconds. (yes, I indexed the joining fields and the search terms). This bites. . . However. I notice that if I do two separate searches it goes quicker (about 2.5 seconds combined). I can do a criteria search on defendants and then put all the resulting case numbers in a temporary table. Then do a join of that temporary table to the much smaller Cases table and do a search on that. I get the same results, and the query time is halved. Ummm, is there any reason why I shouldn't do this? (Other than the inelegance of running two queries instaed of one) Do people do stuff like this for performance reasons? - Steve - 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: Joins are damn slow. . .
Steve Quezadas wrote: However. I notice that if I do two separate searches it goes quicker (about 2.5 seconds combined). I can do a criteria search on defendants and then put all the resulting case numbers in a temporary table. Then do a join of that temporary table to the much smaller Cases table and do a search on that. I get the same results, and the query time is halved. Do an explain on all those queries, post the output here and the time it took to run the queries. -- 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
RE: Joins are damn slow. . .
Your joins are probably not using indexes and the tables could be joining in an inefficient order. Try running an explain on your query to see how it is being done. -Original Message- From: Steve Quezadas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 1:31 AM To: [EMAIL PROTECTED] Subject: Joins are damn slow. . . Maybe I'm a bit naive in saying this but. . . SQL joins are damn slow! - 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
