Hi,
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)
Here's my questions:
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?
2) Does the order of the join conditions in the WHERE clause matter in this
case, when I use AND only, and no parantheses?
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?
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 >
Are there any other relevant pages that I have missed?
TIA
--
Roger
query
---------------------------------------------------------------------
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
