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]
