*horribly unrealistic test warning*
movies table: 1004 entries (id, directorid, name)
directors table: 1765 entries (id, name)
moviedirectors table: 1004 entries (movieid, directorid)
(duplication of data with directorid is purposeful, for the sake of
testing two query types with the same data).
select movies.name, directors.name from movies left join moviedirectors
on movieid = movies.id left join directors on moviedirectors.directorid
= directors.id order by directors.name;
1004 rows, 0.052 secs (average, 10 runs)
select movies.name, directors.name from movies left join directors on
directorid = directors.id order by directors.name;
1004 rows, 0.035 secs (average, 10 runs)
These of course do not represent anything close to real queries.
Doing a select for the list of movies by a given director is 0.02 secs
with the single join, 0.05 secs with the double join. Getting the
director name for a movie by title is 0.00 secs, even using LIKE and
with 66 row result sets:
+----------------+--------+---------------+---------+---------+
| table | type | possible_keys | key | key_len |
+----------------+--------+---------------+---------+---------+
+------+----------------------------+
| rows | Extra |
| movies | ALL | NULL | NULL | NULL |
| 1004 | where used; Using filesort |
| moviedirectors | ref | PRIMARY | PRIMARY | 4 |
| 10 | Using index |
| directors | eq_ref | PRIMARY | PRIMARY | 4 |
| 1 | |
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
---------------------------------------------------------------------
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
