List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 13 2006 3:41pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
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



Thread
Multiple many-to-many SELECTJessica Yazbek9 Feb
  • Re: Multiple many-to-many SELECTRhino10 Feb
    • Re: Multiple many-to-many SELECTsheeri kritzer10 Feb
      • Re: Multiple many-to-many SELECTJessica Yazbek10 Feb
Re: Multiple many-to-many SELECTJessica Yazbek12 Feb
Re: Multiple many-to-many SELECTPeter Brawley12 Feb
  • Re: Multiple many-to-many SELECTSGreen13 Feb
    • Re: Multiple many-to-many SELECTSGreen13 Feb