List:General Discussion« Previous MessageNext Message »
From:Rudy Metzger Date:July 10 2003 10:01am
Subject:RE: order of table joins or where clauses relevant?
View as plain text  
The order of tables in the from does NOT make any difference. The
optimizer (normally) will "rewrite" the kwiri to suit its needs. Also
the order in the WHERE does not make any difference.

You should put all restrictions into the WHERE clause, which is MUCH
faster than putting them into the HAVING. Why? To apply having
conditions you first need to have a result set. So if you already can
limit the size of the result set with WHEREs makes it all that faster.

Order in the FROM does matter when you use STRAIGHT_JOIN.

Please also note that the optimizer only uses keys when it calculates
that using key scan will not require more than scanning at least about
30% of the table records (MyISAM). So with very small tables indexes
will almost never be uses and only slow down inserts/updates/deletes.

Cheers
/rudy

-----Original Message-----
From: nospam@stripped [mailto:nospam@stripped] 
Sent: woensdag 9 juli 2003 18:18
To: mysql@stripped
Subject: order of table joins or where clauses relevant?

As we're on this topic in another thread right now:

Say I have a SELECT query from more than one table and with some
conditions, does it matter in what order I enter the tables in the FROM
clause and in what order the WHERE conditions appear in my query? Or
does it make any difference if I use WHERE or HAVING? (I see that MS
Access likes those HAVINGs...)

Of course my tables contain (maybe very much) more than some 100 records
and are well-indexed, I believe.... but that's not my question for now.

I guess, the MySQL optimizer reads the table and column names in the
specified order and tries to process them the same way, right? Or it
joins the tables in my given order... And when are the resulting records
reduced by matching against my conditions? Maybe someone can tell me a
little bit about performance gains just by doing some 'manual query
optimization' :)

Yves Goergen
www.unclassified.de


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Access deniedAsterix11 Mar
  • Re: Access deniedChristian Mack11 Mar
  • Re: Can mysql handle this load?nospam9 Jul
  • RE: Can mysql handle this load?Andy Eastham9 Jul
  • Re: Can mysql handle this load?Krasimir_Slaveykov10 Jul
  • Left Join - Revistedvernon10 Jul
RE: Can mysql handle this load?Mike Hillyer9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: order of table joins or where clauses relevant?Rudy Metzger10 Jul
RE: Left Join - RevistedRudy Metzger11 Jul
RE: Can mysql handle this load?Rudy Metzger14 Jul