On Tue, February 7, 2006 10:46 am, [EMAIL PROTECTED] wrote: > "David T. Ashley" <[EMAIL PROTECTED]> wrote on 07/02/2006 14:03:04: > >> a)Will MySQL allow joins that involve more than two tables (in my case, >> perhaps as many as 5)? >> >> b)Can limits on a key field be included in the join in the same SQL >> statement as does the join, i.e. ... WHERE N>3 AND N<20 ... or something >> like that. > > Yes, you can do multi-way joins, and people often do. My biggest is 3-way, > but some people do at least 5-way. Beware that it is easy to specify > operations which will heavily load the system if you are not careful. > > The constraints in the WHERE statement are *logically* and syntactically > done on the huge table produced by the joins. However, the MySQL optimiser > is not stupid and will perform the filter upstream of the JOIN where > possible. Some experimentation and use of the EXPLAIN statement may be > necessary to find the best ordering for queries.
My thought process was that if all the fields involved--both in the JOIN and limits on any fields--were key fields, then the operation should be approximately O(log N). But, now that I think about it: a)A simple limit operation or search operation on a KEY field should be O(log N) (i.e. doesn't MySQL build index tables or something on key fields to get approximately that behavior?), BUT b)I think you are right about the caution ... just thinking about it, it isn't clear that a JOIN will be O(log N) when the only condition is something like (table1.a = table2.b). It seems that a limiting condition (sex=MALE or date>birthday1 and date<birthday2 or whatever) would have to be applied first to a key field (by the optimizer?) or else the JOIN would be something like O(N) or maybe even worse. Thanks for pointing out the EXPLAIN keyword. That may be helpful. I'd be curious on any perspective ... what computational complexity is an inner join with no other conditions (i.e. the only condition is table1.a = table2.b)? I'd guess it isn't O(log N). Well that concludes my newbie questions. Thanks for the help. I'll now begin using MySQL ... ----------------------------------- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
