List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 29 2005 2:20pm
Subject:Re: comma-separated JOINS
View as plain text  
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" <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 
them 
> 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 
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/mysql?unsub=1
> 

Thread
comma-separated JOINSMatt Monaco29 Nov
  • Re: comma-separated JOINSPeter Brawley29 Nov
  • Re: comma-separated JOINSMatt Monaco29 Nov
    • Re: comma-separated JOINSSGreen29 Nov
    • Re: comma-separated JOINSMatt Monaco29 Nov