I'm trying to determine the more efficient syntax between a left join and
this other thing, maybe its a cross join or just a join. I used explain and
the left join has more NULL Keys but seemingly less rows to look at. Are
the multiplied number of rows pretty much the word on efficiency. They both
take the same time to execute. Also, this is using a union in 4.09. Do I
need to upgrade to 4.1 to get an accurate comparison or just a more
thorough one. (Where do you get 4.1?)
In MySQL version 4.1 the EXPLAIN output was changed to work better with
constructs like UNIONs, subqueries and derived tables. Most notable is the
addition of two new columns: id and select_type.
Here are the two different queries with their explain tables.
LEFT JOIN
===========
mysql> explain (select people.id, concat(lastname, ', ', firstname) as
name, title, organizations.org, ptype, groupname, mee
notes, 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)
-> union
-> (select people.id, concat(lastname, ', ', firstname) as name, title,
organizations.org, ptype, '' as groupname, meeti
tes, dtime, edtime
-> from people
-> left join organizations on people.org=organizations.id
-> left join otype on organizations.otype=otype.id
-> left join mpeople on people.id=mpeople.pid
-> left join meetings on mpeople.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 ptype, name, dtime, meeting;
+---------------+--------+---------------+---------+---------+---------------------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+---------------+--------+---------------+---------+---------+---------------------+------+--------------------------+
| people | ALL | NULL | NULL | NULL | NULL
| 17 | |
| organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org
| 1 | |
| otype | eq_ref | PRIMARY | PRIMARY | 2 |
organizations.otype | 1 | |
| pgroups | ALL | pid | NULL | NULL | NULL
| 14 | |
| groups | eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid
| 1 | |
| mgroups | ALL | gid | 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 | Using where; Using index |
| people | ALL | NULL | NULL | NULL | NULL
| 17 | |
| organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org
| 1 | |
| otype | eq_ref | PRIMARY | PRIMARY | 2 |
organizations.otype | 1 | |
| mpeople | ALL | pid | NULL | NULL | NULL
| 35 | |
| meetings | eq_ref | PRIMARY | PRIMARY | 2 | mpeople.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 | Using where; Using index |
| people | index | NULL | PRIMARY | 2 | NULL
| 17 | Using index |
| organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org
| 1 | Using index |
| otype | eq_ref | PRIMARY | PRIMARY | 2 |
organizations.otype | 1 | Using index |
| pgroups | index | pid | PRIMARY | 2 | NULL
| 14 | Using index |
| groups | eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid
| 1 | Using index |
| mgroups | index | gid | PRIMARY | 2 | NULL
| 6 | Using index |
| meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid
| 1 | Using index |
| rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid
| 1 | Using index |
| locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid
| 1 | Using index |
| events | eq_ref | PRIMARY | PRIMARY | 2 | meetings.eid
| 1 | Using index |
+---------------+--------+---------------+---------+---------+---------------------+------+--------------------------+
28 rows in set (0.01 sec)
Other
===============
mysql> explain (select people.id, concat(lastname, ', ', firstname) as
name, title, organizations.org, ptyp
notes, dtime, edtime
-> from people, organizations, otype, pgroups, groups, mgroups,
meetings, 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)
-> union
-> (select people.id, concat(lastname, ', ', firstname) as name, title,
organizations.org, ptype, '' as
tes, dtime, edtime
-> from people, organizations, otype, mpeople, meetings, rooms,
locations, events
-> where people.org=organizations.id
-> and organizations.otype=otype.id
-> and people.id=mpeople.pid
-> and mpeople.mid=meetings.id
-> and meetings.rid=rooms.id
-> and rooms.lid=locations.id
-> and meetings.eid=events.id
-> and events.id=2)
-> order by ptype, name, dtime, meeting;
+---------------+--------+---------------+---------+---------+---------------------+------+-------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+---------------+--------+---------------+---------+---------+---------------------+------+-------------+
| events | const | PRIMARY | PRIMARY | 2 | const
| 1 | |
| pgroups | ALL | pid,gid | NULL | NULL | NULL
| 14 | |
| mgroups | ALL | mid,gid | NULL | NULL | NULL
| 6 | Using where |
| 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.otype | 1 | |
| meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid
| 1 | Using where |
| rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid
| 1 | |
| locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid
| 1 | |
| events | const | PRIMARY | PRIMARY | 2 | const
| 1 | |
| mpeople | ALL | mid,pid | NULL | NULL | NULL
| 35 | |
| people | eq_ref | PRIMARY | PRIMARY | 2 | mpeople.pid
| 1 | |
| organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org
| 1 | |
| otype | eq_ref | PRIMARY | PRIMARY | 2 |
organizations.otype | 1 | |
| meetings | eq_ref | PRIMARY | PRIMARY | 2 | mpeople.mid
| 1 | Using where |
| rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid
| 1 | |
| locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid
| 1 | |
| groups | index | NULL | PRIMARY | 2 | NULL
| 4 | Using index |
| locations | index | NULL | PRIMARY | 2 | NULL
| 4 | Using index |
| mgroups | index | NULL | PRIMARY | 2 | NULL
| 6 | Using index |
| events | index | NULL | PRIMARY | 2 | NULL
| 2 | Using index |
| otype | index | NULL | PRIMARY | 2 | NULL
| 10 | Using index |
| meetings | index | NULL | PRIMARY | 2 | NULL
| 12 | Using index |
| people | index | NULL | PRIMARY | 2 | NULL
| 17 | Using index |
| organizations | index | NULL | PRIMARY | 2 | NULL
| 9 | Using index |
| pgroups | index | NULL | PRIMARY | 2 | NULL
| 14 | Using index |
| rooms | index | NULL | PRIMARY | 2 | NULL
| 15 | Using index |
+---------------+--------+---------------+---------+---------+---------------------+------+-------------+
28 rows in set (0.09 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
