CODEWITHSUNDEEP
mysqlsqlquery-optimization
Stanislav Stankov
Stanislav Stankov

Reputation: 135

mysql query with SELECT IN SELECT in more efficient way

Please help me optimize query about getting reccomended (rec) for movies. I have many records and query run quite slow. The following query run for 2 mins

   SELECT rec.toMovieID, sum(rec.score) 
   FROM rec 
   WHERE movieID in 
      (SELECT movieid as movieID FROM userFavorites as ufv WHERE ufv.userid = 29)
   GROUP BY rec.toAMovieID
   ORDER BY rec.score DESC
   LIMIT 10

Do you think I can optimize it more?

Upvotes: 0

Views: 52

Answers (2)

Barry
Barry

Reputation: 3723

You can use an inner join instead of a subselect

SELECT
    rec.toMovieID,
    sum(rec.score) 
FROM rec INNER JOIN userFavorites ON rec.movieID = userFavorites.movieid
WHERE
    userid = 29
GROUP BY rec.toAMovieID
ORDER BY rec.score DESC
LIMIT 10

You should set indexes on rows in where clause, at least for movieid and userid. (If not allready done)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use exists:

   SELECT rec.toMovieID, sum(rec.score) 
   FROM rec r
   WHERE EXISTS (SELECT 1 FROM userFavorites as ufv WHERE ufv.userid = 29 and ufv.MovieId = r.MovieId)
   GROUP BY rec.toAMovieID
   ORDER BY rec.score DESC
   LIMIT 10;

You have to be careful using a join because of duplicate records.

Upvotes: 1

Related Questions