MySQL proper way to SELECT with many tables involved

I have movie database that has these tables: new_movies, ratings, critic_ratings, colors

I'm trying to execute this SELECT statement which will combine these 4 tables on the same movie using 'mid' (movie id):

SELECT DISTINCT 
   new_movies.*,
   movies_db.*,
   ratings.rating,
   ratings.count,color,
   critic_ratings.rating AS critic_ratings 
FROM 
   new_movies 
INNER JOIN 
   movies_db 
ON 
   new_movies.mid = movies_db.mid 
LEFT JOIN 
   ratings 
ON 
   new_movies.mid = ratings.mid 
LEFT JOIN 
   colors 
ON 
   new_movies.mid = colors.mid 
LEFT JOIN 
   critic_ratings 
ON 
   new_movies.mid = critic_ratings.mid 
ORDER BY 
   title ASC

But I get th开发者_如何学编程is error:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

How do I properly do this query?

If you don't want to enable big selects, you could reform this using correlated sub-queries. (I don't know if you'll still hit the limit or not though.)

SELECT DISTINCT 
   new_movies.*,
   movies_db.*,
   (SELECT rating FROM ratings        WHERE new_movies.mid = ratings.mid) AS rating,
   (SELECT count  FROM ratings        WHERE new_movies.mid = ratings.mid) AS rating_count,
   (SELECT color  FROM colors         WHERE new_movies.mid = colors.mid)  AS colour,
   (SELECT rating FROM critic_ratings WHERE new_movies.mid = critic_ratings.mid) AS critic_ratings
FROM 
  new_movies 
INNER JOIN 
  movies_db 
    ON new_movies.mid = movies_db.mid 
ORDER BY 
   title ASC

Also, worth a test to see if the LEFT JOINs are actually the cause, can you execute the following?

SELECT DISTINCT 
   new_movies.*,
   movies_db.*
FROM 
  new_movies 
INNER JOIN 
  movies_db 
    ON new_movies.mid = movies_db.mid 
ORDER BY 
   title ASC

why do you have a movies and a new_movies table? surely a release date field would be sufficient for that - would cut out a join too...

to that end I would create a view of that data and query that instead.

But back to your query:

SELECT DISTINCT
    new_movies.*,
    movies_db.*,
    ratings.rating,
    ratings.count,
    color,
    critic_ratings.rating AS critic_ratings
FROM
    new_movies
INNER JOIN
    movies_db
ON
    new_movies.mid = movies_db.mid
LEFT JOIN
    ratings
ON
    new_movies.mid = ratings.mid
LEFT JOIN
    colors
ON
    new_movies.mid = colors.mid
LEFT JOIN
    critic_ratings
ON
    new_movies.mid = critic_ratings.mid
ORDER BY
    title ASC

I can't see anything obvious... perhaps you can post the results of an explain query?

There is no problem with your query per se. It's just that you're selecting all movies (no WHERE, no LIMIT) and since you're joining ratings for e.g., it will join all ratings to each movie. You are just reaching the max amount of data allowed for joins.

I'm not sure why you'd need to select all movies. Perhaps you can use a limit. Otherwise you can just try the solutions in the error message.