* Michael T. Babcock > Its well appreciated to see examples of what works and what doesn't too; > its nice to know how to get one's queries in an order that makes the > MySQL optimizer's life easier. I'm trying to decide if there's some > logical way to inform MySQL that it can reorder a series of JOINs any > way it likes for best performance.
AFAIK, mysql will try to optimize by doing the joins in the fastest possible way, regardless of what order they are listed in the select statement. There are some exceptions to this: "a LEFT JOIN b" and "a STRAIGHT_JOIN b" will always read a before b. > At any rate, try ordering your JOINs > in such a way as to eliminate the most rows as soon as possible, and see > if that makes a difference; if your 'smallest tables first' query does > in fact do that, then great. In my experience, the only way to force the order of the joins is to use STRAIGHT_JOIN, and even when you use STRAIGHT_JOIN, you don't always get what you think: SELECT * FROM a STRAIGHT_JOIN b,c,d,e LEFT JOIN f ON f.id=e.id The join order of this select might become for instance c,d,e,a,b,f. In other words, you have only forced b to be read after a, not a to be the first table to read... only if you replace all commas with STRAIGHT_JOIN(1), you get the order you dictate. (Normally not a good idea, the optimizer usually finds the best join order.) Another thing to be aware of: The distribution of data in your tables affect the join optimizer. Consider a multi-table join with this condition: WHERE ... firstname.name = 'Roger' and lastname.name = 'Baklund' ...and the same statement with this condition: WHERE ... firstname.name = 'Roger' and lastname.name = 'Olsen' (Olsen is a very common last name in Norway, Baklund is not). For the first statement, the optimizer may decide to read the 'lastname' table first, because there are relatively few occurences of "Baklund", while there are many occurences of "Roger". For the second statement, the 'firstname' table is read first, because there are many more occurences of "Olsen" in the lastname table compared to "Roger" in the firstname table. This optimization is normally a good thing, and it is lost if you use STRAIGHT_JOIN. (1) or use the STRAIGHT_JOIN option for the SELECT statement -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
