hello i will explain , in my design , i usually join tables with id's table 1 catID - primary unique category
table 2 productID - primary unique product_name catID what is the best way to select i usually do select t2.product_name, t1.category, t1.catID from table2 t2 LEFT JOIN table1 t1 ON t2.catID=t1.catID then sometimes select t2.product_name, t1.category, t1.catID from table2 t2 LEFT JOIN table1 t1 ON t2.catID=t1.catID where t2.productID=1 how can i optimise this further , is doing this just to get a category name for example from table2 using an id which i dont usually set as a key be slow ? should i setup catID on table2 as an index key and how ? is doing it this way faster and get the same results ? select t2.product_name, t1.category, t1.catID from table2 t2, table1 t1 where t2.catID=t1.catID and t2.productID=1 i have found the way i have been doing this very slow on some massive tables and would like to optimise the join if possible -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: Wednesday, February 26, 2003 11:26 PM To: [EMAIL PROTECTED] Subject: Re: JOIN compared to WHERE clause In article <[EMAIL PROTECTED]>, "Dan Rossi" <[EMAIL PROTECTED]> writes: > hi i was wondering which statement is quicker getting results when joining > tables ? i presume something like FROM foo f LEFT JOIN bar b ON f.id=b.id is > quiker than WHERE f.id=b.id ? Your question does not make sense. A LEFT JOIN returns different results than an INNER JOIN, so there's no point in performance comparisons. [Filter fodder: SQL query] --------------------------------------------------------------------- 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
