Hi Roger, > consider this select statement as an example: > > SELECT d_person.dispname, d_title.title > FROM d_person, d_title, s_first, s_last, t_person, l_first, l_last > WHERE > t_person.dperson_id = d_person.dperson_id AND > t_person.dtitle_id = d_title.dtitle_id AND > l_first.fname_id = s_first.fname_id AND > l_last.lname_id = s_last.lname_id AND > t_person.person_id = l_first.person_id AND > t_person.person_id = l_last.person_id AND > s_first.first = "roger" AND > s_last.last LIKE "bakl%" > > The tables with prefix d_ contains 'display information', the prefix l_ > indicates a 'link' between two tables (M:M), prefix s_ is the 'searchable' > tables, and t_ is the real entities, in this case a person. This is a fully > normalized database, a t_person instance can have multiple s_first and > s_last, and each s_first/s_last can have multiple t_person. A t_person can > have only one d_person and only one d_title. > > 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. > Here's my questions: ?The preceding was a zero-th question/doesn't count? > 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. > 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. > 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! > I have read the relevant sections in the manual: > <URL: http://www.mysql.com/doc/J/O/JOIN.html > > <URL: http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html > > <URL: http://www.mysql.com/doc/S/E/SELECT.html > Expected no less. > Are there any other relevant pages that I have missed? The whole of Chapter 5 is about optimisation. In particular you will enjoy 5.2 Optimising SELECTs and Other Queries. Which leads you straight into 5.2.1 EXPLAIN Syntax. "When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order." 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? 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
