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
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.
Unimin Corporation - Spruce Pine
"Matt Monaco" <mmonaco3@stripped> 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
> in order?
> "Peter Brawley" <peter.brawley@stripped> wrote in message
> news:438BAFCA.4030508@ style="color:#666">stripped...
> > 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
> > one reason specifiying joins with JOIN ... ON .. is almost always
> > preferable--it entirely disambiguates the join for the writer,
> > 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
> >>the FROM clause, is the ON argument normally associated with a join
> >>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
> >>// 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
> >>do not deal with the relationships. I would like to assure the
> >>of this query.
> >>Thanks in advance,
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date:
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1