> > > 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 >
=All true! However we can either accept what the optimiser comes up with or direct it to follow our own 'tunes'. =Also, whilst it is true to say that the fewer table permutations there are, the faster will be the (fewer) joins (second in 'duh' value only to the suggestion that if the table had fewer rows then the query would speed up!***), the reality is that few of us introduce joins without having some purpose for doing so! Whereupon the question becomes, how do I sequence the series of joins/conditions to optimise the response time - I was going for the latter! *** user question: my laptop is too heavy, what can I do to lighten it? HD answer: delete all your files, then you won't have so much data to carry! > > > Here's my questions: > > ?The preceding was a zero-th question/doesn't count? > I did not think it was a question, no... :) =Is this a question I see before me...? > > > 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 =However, what I mentioned above still applies: no the sequence of the FROM/WHERE clauses does not matter in the sense that the optimiser can exercise its rules/skills to choose a different order AND/OR we can direct the sequence... - I think I'm telling you stuff you're way past??? > > > 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. =When the going gets tough, the tough... (go out for ice cream?) =There are a few techniques that get pulled out of the toolbox for this sort of thing, but most/all depend upon your having some understanding of the spread and nature of the data values in the pertinent columns/fields. For example you could vary the wording of the query/optimiser override if you have performed some sort of frequency analysis on the data-spread. =There are others on the list who are 'into' this sort of question. Perhaps Jeremy's looking for such material for his book? > > > 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 :)) =whereas my parents were always asking me to 'explain' myself - most have a range of (uncomplimentary) ways of describing me... > I can of course test this myself, just hoped somebody knew... :) =you've read the manual. Because the myriad uses are so varied it is difficult to transfer the 'general' to a 'specific situation'. =My observation: when I've bothered to look either the optimiser is so far out of touch that it seems to be playing in a different game (bad hair day?), or it has done such a good job that I can experiment all day and still probably only make things slower! I figure the MySQL boys have forgotten more about relational algebra than I can remember... Regards, =dn --------------------------------------------------------------------- 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
