MySQL WHERE IN（SELECT id from ...）最快的方法？

Question

I am using a query like this:

SELECT * FROM videos WHERE id IN (SELECT video_id FROM videos_tags WHERE tag_id = 2)

to search videos with the same tags, but with about 8'000 videos this query is too slow. How can I make this query faster? Maybe a JOIN is faster? Something like select * from videos_tags left join videos on (videos_tags.video_id = videos.id) where videos_tags.tag_id = 2 is faster?

Answer 1

You can alternative use JOIN instead of subquery.

SELECT  DISTINCT a.*
FROM    videos a
        INNER JOIN video_tags b
            ON a.ID = b.video_ID
WHERE   b.tag_id = 2

For faster performance, create a foreign key constraint on video_tags table which references on videos. Example, (make sure that video.ID is a Primary Key or a Unique Key)

ALTER TABLE video_tags ADD CONSTRAINT vt_fk 
            FOREIGN KEY (video_ID) REFERENCES video(ID)

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins