How to get similar objects based on tags

Question

I have three tables like these:

• movie: id, name

• tag: id, name, value

• tagged: id, movie (FK), tag (FK)

Thus, each movie has its own set of tags. I need to get similar movies based on a set of tags. I want to say that 10 films are sorted by the number of matching tags.

If I create a view as shown below, it makes MySQL leave. There are 30k + entries in the "tag" and "tagged" tables.

create view relatedtags as 

select
    entityLeft.id as id,
    entityRight.id as rightId,
    count(rightTagged.id) as matches

from
    entity as entityLeft join tagged as leftTagged on leftTagged.entity = entityLeft.id, 
    entity as entityRight join tagged as rightTagged on rightTagged.entity = entityRight.id

where leftTagged.tag = rightTagged.tag
and entityLeft.id != entityRight.id
group by entityLeft.id, entityRight.id

Answer 1

This will return a list of all movies in which there is at least 1 tag with a given <current_movie_id>, ordered by reducing the number of tags in total

SELECT movie.*, count(DISTINCT similar.tag) as shared_tags FROM movie INNER JOIN 
    ( tagged AS this_movie INNER JOIN tagged AS similar USING (tag) )
    ON similar.movie = movie.id
WHERE this_movie.movie=<current_movie_id>
AND   movie.id != this_movie.movie
GROUP BY movie.id
ORDER BY shared_tags DESC

, -