Re: comma-separated JOINS
The sequence you use to JOIN a set of tables is sometimes determined completely by the logic of the JOIN. You should perform all of your CROSS JOINs and INNER JOINs first (a comma is equivalent to a CROSS JOIN but starting with 5.0.12 the comma has a LOWER evaluation priority than an explicit JOIN so watch out!!) then list your LEFT or RIGHT JOINs. It is very difficult to have both LEFT and RIGHT joins in the same query and get a correct result. You can nearly always transform a query containing both LEFT JOIN and a RIGHT JOIN into a query using a CROSS JOIN and a LEFT JOIN. When given the opportunity in a query where several equivalent constructions are available (as in a query that uses nothing but INNER JOINs) I try to list the smallest table first. It may not have the least number of physical records but should have the fewest number of rows returned. JOINs are geometrically expensive operations and the fewer rows you need to evaluate between stages of your JOINs, the more likely you are to have better performance. Try to keep the columns that are references to or are referenced by other tables indexed. The MySQL query engine (at least until 5.0) will use just one index per table per query. Which tables the columns come from in your results does not matter so long as you construct your table reference correctly. Above all else, I strongly discourage the use of comma-joins. The explicit JOIN syntax is not only less ambiguous but is the only way to declare an outer JOIN with MySQL. When you use the explicit JOIN syntax, you are also less likely to form unintentional Cartesian products which can absolutely clobber a query's performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matt Monaco [EMAIL PROTECTED] wrote on 11/29/2005 12:22:45 AM: What would be the most efficient order to join in? Say I have one main table with most columns (I assume this should be the main table of the query) then each table relates to the next, is it as simple as putting them in order? Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Matt, When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? There's no ON clause for a join specified by a WHERE clause, and that's one reason specifiying joins with JOIN ... ON .. is almost always preferable--it entirely disambiguates the join for the writer, readers, and those others who later will have to divine what you meant :-) . PB - Matt Monaco wrote: When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comma-separated JOINS
I just wanted to thank you for such a thorough response, it has helped me a lot as I've always ignored the existence of joins. Thanks, Matt [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] The sequence you use to JOIN a set of tables is sometimes determined completely by the logic of the JOIN. You should perform all of your CROSS JOINs and INNER JOINs first (a comma is equivalent to a CROSS JOIN but starting with 5.0.12 the comma has a LOWER evaluation priority than an explicit JOIN so watch out!!) then list your LEFT or RIGHT JOINs. It is very difficult to have both LEFT and RIGHT joins in the same query and get a correct result. You can nearly always transform a query containing both LEFT JOIN and a RIGHT JOIN into a query using a CROSS JOIN and a LEFT JOIN. When given the opportunity in a query where several equivalent constructions are available (as in a query that uses nothing but INNER JOINs) I try to list the smallest table first. It may not have the least number of physical records but should have the fewest number of rows returned. JOINs are geometrically expensive operations and the fewer rows you need to evaluate between stages of your JOINs, the more likely you are to have better performance. Try to keep the columns that are references to or are referenced by other tables indexed. The MySQL query engine (at least until 5.0) will use just one index per table per query. Which tables the columns come from in your results does not matter so long as you construct your table reference correctly. Above all else, I strongly discourage the use of comma-joins. The explicit JOIN syntax is not only less ambiguous but is the only way to declare an outer JOIN with MySQL. When you use the explicit JOIN syntax, you are also less likely to form unintentional Cartesian products which can absolutely clobber a query's performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matt Monaco [EMAIL PROTECTED] wrote on 11/29/2005 12:22:45 AM: What would be the most efficient order to join in? Say I have one main table with most columns (I assume this should be the main table of the query) then each table relates to the next, is it as simple as putting them in order? Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Matt, When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? There's no ON clause for a join specified by a WHERE clause, and that's one reason specifiying joins with JOIN ... ON .. is almost always preferable--it entirely disambiguates the join for the writer, readers, and those others who later will have to divine what you meant :-) . PB - Matt Monaco wrote: When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comma-separated JOINS
When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comma-separated JOINS
Matt, When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? There's no ON clause for a join specified by a WHERE clause, and that's one reason specifiying joins with JOIN ... ON .. is almost always preferable--it entirely disambiguates the join for the writer, readers, and those others who later will have to divine what you meant :-) . PB - Matt Monaco wrote: When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comma-separated JOINS
What would be the most efficient order to join in? Say I have one main table with most columns (I assume this should be the main table of the query) then each table relates to the next, is it as simple as putting them in order? Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Matt, When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? There's no ON clause for a join specified by a WHERE clause, and that's one reason specifiying joins with JOIN ... ON .. is almost always preferable--it entirely disambiguates the join for the writer, readers, and those others who later will have to divine what you meant :-) . PB - Matt Monaco wrote: When using JOINS by the simply supplying a comma separated list of tables in the FROM clause, is the ON argument normally associated with a join intended to be addressed in the WHERE clause, or should ON still be used? // Comma separated join SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id; // Actual JOIN clause SELECT u.*, a.city FROM users u INNER JOIN addresses a ON u.id=a.user_id; // Query style in question SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id; If not ON, is there at least another viable argument? The reason I'm interested is for a query involving 5 or 6 tables and WHERE arguments which do not deal with the relationships. I would like to assure the efficiency of this query. Thanks in advance, Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
