CODEWITHSUNDEEP
phpmysqldatabaseperformance
Mattia Manzo Manzati
Mattia Manzo Manzati

Reputation: 586

MySQL WHERE IN (SELECT id from ...) fastest approach?

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?

Upvotes: 1

Views: 12906

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

What version of MySQL are you using? The problem with the optimization of in seems to be fixed in version 5.6.

I think the fastest version otherwise is to use exists:

SELECT *
FROM videos
WHERE exists (SELECT 1
              FROM videos_tags
              WHERE video_tags.tag_id = 2 and video_tags.video_id = videos.id
             )

This version also has the benefit that you do not run into the possibility of multiple matches (in the tags table) resulting in multiple identical rows. This would happen, for instance, if you changed the query to look for tags 2 and 3, and some videos had both.

For optimal performance, have an index on videos_tags(tag_id, video_id).

Upvotes: 2

John Woo
John Woo

Reputation: 263723

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:

Upvotes: 6

Related Questions