oops! I hit SEND before I was done. (BAD FINGERS!)
SELECT *
FROM movies
LEFT JOIN director_movies
ON director_movies.movie_id=movies.id
LEFT JOIN director
ON director_movies.director_id = director.id
LEFT JOIN country_movies
ON country_movies.movie_id=movies.id
LEFT JOIN country
ON country_movies.country_id = country.id
LEFT JOIN producer_movies
ON producer_movies.movie_id=movies.id
LEFT JOIN producer
ON producer_movies.producer_id = producer.id
WHERE movies.id = 123
Now this may not be as fast as doing this in stages. First stage you
collect all of the secondary id's into a temporary table. Second stage you
hit the master tables and resolve the secondary id's into their actual
values. Once you get above 7 or so JOINs per query, performance tends to
degrade.
CREATE TEMPORARY TABLE tmpMiddle
SELECT movies.id, director_movies.director_id, country_movies.country_id,
producer_movies.producer_id
FROM movies
LEFT JOIN director_movies
ON director_movies.movie_id=movies.id
LEFT JOIN country_movies
ON country_movies.movie_id=movies.id
LEFT JOIN producer_movies
ON producer_movies.movie_id=movies.id
WHERE movies.id = 123 ;
SELECT *
FROM tmpMiddle
LEFT JOIN movies
ON movies.id = tmpMiddle.id
LEFT JOIN director
ON tmpMiddle.director_id = director.id
LEFT JOIN country
ON tmpMiddle.country_id = country.id
LEFT JOIN producer
ON tmpMiddle.producer_id = producer.id;
DROP TEMPORARY TABLE tmpMiddle;
This reduces the # of joins from 8 to 4 and based on your initial
description you will probably have more than this. Again, this is just
another way to look at the issue.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
