* DL Neil > > I'm quite sure it does not matter if I write > > t_person.dperson_id = d_person.dperson_id AND > > or > > d_person.dperson_id = t_person.dperson_id AND > > but please tell me if it does... :o) > > Logically there is no difference, however prevailing wisdom suggests > that you put the 'smallest number' first, for speed (fewer look-ups). So > if there are fewer t_person.dperson_id rows than there are > d_person.dperson_id rows, put them in the former sequence, else the > latter.
Ok... I thought the join optimizer decided in wich order the tables are joined? >From the manual: "The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimiser (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check." <URL: http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html > > > Here's my questions: > > ?The preceding was a zero-th question/doesn't count? I did not think it was a question, no... :) > > 1) Does the order of the tables in the FROM clause matter? I know it > matters > > with LEFT JOIN and USING(), and of course if I use STRAIGHT_JOIN, but > in > > this case? > > Sorry, can't answer this - am trying to find the time to study the > different ways of expressing a join/FROM clause. ok > > 2) Does the order of the join conditions in the WHERE clause matter in > this > > case, when I use AND only, and no parantheses? > > Extend the previous answer: Try to use the 'earlier' conditions to > exclude as many rows/combinations of rows as possible from the later > conditions. Yes... I guess what I really want to know is how the internal join optimization works, when it comes to deciding the order of the joining. I am extending the level of normalization in an existing database, and I need to do some selects dynamically. The existing code is already getting messy, and with the added complexity of the additional tables in the new data model, I need to rewrite the dynamic select generation (DSG) code. The current DSG have a lot of business rules inluded in the code. I am trying to make a more generic DSG, using the DB scheme and a minimum of additional meta data. If I do not need to care about the order of the join conditions, it would be easier to make the dynamic selects. > > 3) Would it matter if I added "l_last.person_id = l_first.person_id > AND" to > > avoid too many reads of the t_person table, or will the optimizer > figure > > this out by itself? > > Check out what the optimiser does, and only 'interfere'/offer guidance > if things are not going the way you would like! Yes... again, this was meant as a more 'theoretic' question, and I know how to use EXPLAIN... (actually, I prefer desc :)) I can of course test this myself, just hoped somebody knew... :) > The manual doesn't get into this sort of thing as much as some of the > books do - and you already know the book list... Some of the > intermediate/advanced SQL (not product name) database tutorials online > get into optimisation. > > Ok? Sure, thanks! :) -- 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
