same results and both varying between .01, .02 seconds to execute.
6 of one half dozen of another or is there an advantage to one?
My guess is that the second syntax is preferred given the reduced row count
for events in it's explain table.
The first statement uses left joins, the second use's Brent suggestion.
Also, is the second statement what they call a straight join?
thanks, jb
mysql> explain select people.id, concat(lastname, ', ', firstname) as name,
titl
e,
-> organizations.org, ptype, groupname, meeting, room, location,
-> moderator,addnotes, dtime, edtime
-> from people
-> left join organizations on people.org=organizations.id
-> left join otype on organizations.otype=otype.id
-> left join pgroups on people.id=pgroups.pid
-> left join groups on pgroups.gid=groups.id
-> left join mgroups on pgroups.gid=mgroups.gid
-> left join meetings on mgroups.mid=meetings.id
-> left join rooms on meetings.rid=rooms.id
-> left join locations on rooms.lid=locations.id
-> left join events on meetings.eid=events.id
-> where events.id=2 order by type;
+---------------+--------+---------------+---------+---------+------------------
---+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+---------------+--------+---------------+---------+---------+------------------
---+------+---------------------------------+
| people | ALL | NULL | NULL | NULL | NULL
| 17 | Using temporary; Using filesort |
| organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org
| 1 | |
| otype | eq_ref | PRIMARY | PRIMARY | 2 |
organizations.oty
pe | 1 | |
| pgroups | ALL | NULL | NULL | NULL | NULL
| 14 | |
| groups | eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid
| 1 | |
| mgroups | ALL | NULL | NULL | NULL | NULL
| 6 | |
| meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid
| 1 | |
| rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid
| 1 | |
| locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid
| 1 | |
| events | eq_ref | PRIMARY | PRIMARY | 2 | meetings.eid
| 1 | where used; Using index |
+---------------+--------+---------------+---------+---------+------------------
---+------+---------------------------------+
10 rows in set (0.00 sec)
=======================================================================
=======================================================================
mysql> explain select people.id, concat(lastname, ', ', firstname) as name,
titl
e,
-> organizations.org, ptype, groupname, meeting, room, location,
-> moderator, addnotes, dtime, edtime
-> from people, organizations, otype, pgroups, groups, mgroups,
meeting
s, rooms, locations, events
-> where people.org=organizations.id
-> and organizations.otype=otype.id
-> and people.id=pgroups.pid
-> and pgroups.gid=groups.id
-> and pgroups.gid=mgroups.gid
-> and mgroups.mid=meetings.id
-> and meetings.rid=rooms.id
-> and rooms.lid=locations.id
-> and meetings.eid=events.id
-> and events.id=2 order by type;
+---------------+--------+---------------+---------+---------+------------------
---+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+---------------+--------+---------------+---------+---------+------------------
---+------+---------------------------------+
| events | const | PRIMARY | PRIMARY | 2 | const
| 1 | Using temporary; Using filesort |
| pgroups | ALL | NULL | NULL | NULL | NULL
| 14 | |
| mgroups | ALL | NULL | NULL | NULL | NULL
| 6 | where used |
| people | eq_ref | PRIMARY | PRIMARY | 2 | pgroups.pid
| 1 | |
| groups | eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid
| 1 | |
| organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org
| 1 | |
| otype | eq_ref | PRIMARY | PRIMARY | 2 |
organizations.oty
pe | 1 | |
| meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid
| 1 | where used |
| rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid
| 1 | |
| locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid
| 1 | |
+---------------+--------+---------------+---------+---------+------------------
---+------+---------------------------------+
10 rows in set (0.02 sec)
---------------------------------------------------------------------
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
