CODEWITHSUNDEEP
mysqlsql
Christopher
Christopher

Reputation: 5

Select with inner join TABLE1 OR inner join TABLE2

We have a videos table with several million lines, we would like to select only the videos of a few categories and a few tags.

Is there a way to do it in a more optimized way than this query?

Thank you for your help and feedback.

SELECT videos.id, videos.title FROM videos 
WHERE videos.active = 1 AND videos.deleted IS NULL AND videos.segment = 1
AND (
    videos.id IN(
        SELECT videos.id FROM videos
        INNER JOIN categories_videos on categories_videos.video_id = videos.id
        INNER JOIN categories on categories.id = categories_videos.category_id AND categories.id = 59
        WHERE videos.active = 1 AND videos.deleted IS NULL AND videos.segment = 1
    )
    OR videos.id IN(
        SELECT videos.id FROM videos
        INNER JOIN tags_videos on tags_videos.video_id = videos.id
        INNER JOIN tags on tags.id = tags_videos.tag_id AND tags.id = 231014
        WHERE videos.active = 1 AND videos.deleted IS NULL AND videos.segment = 1
    )
)

Upvotes: 0

Views: 166

Answers (2)

Rick James
Rick James

Reputation: 142453

  • How many Categories can a video be associated with? How many Tags? If both are more than 1, there is very little excuse to keep them separate.

  • Using a many-to-many table to match Tags with Videos is costly. (I assume the tags include actor(s), director(s), genre(s), location(s), etc. That could easily lead to 100M rows in that table.

  • You may want to match 2 tags at once. Performance takes another hit.

  • If there is only one Category, put it in the video table -- no Join needed.

  • I have found that things like "Tag" should have the string in the many-to-many table, thereby merging tags_videos and tags tables:

    CREATE TABLE `tags` (
    video_id INT UNSIGNED NOT NULL,
    tag VARCHAR(100) NOT NULL,
    PRIMARY KEY(video_id, tag),  -- useful for getting tags for a video
    INDEX(tag, video_id)         -- useful for getting videos for a tag
      ) ENGINE=InnoDB;
    -- No auto_increment; it would just be clutter and slow things down

When searching for either of 2 tags:

  FROM videos AS v
  JOIN tags AS t
  WHERE t.tag IN ('comedy', 'John Wayne')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

I would just express this as a union query:

SELECT v.id, v.title
FROM videos v
INNER JOIN categories_videos cv ON cv.video_id = v.id
INNER JOIN categories c ON c.id = cv.category_id AND c.id = 59
WHERE v.active = 1 AND v.deleted IS NULL AND v.segment = 1
UNION ALL
SELECT v.id, v.title
FROM videos v
INNER JOIN tags_videos tv ON tv.video_id = v.id
INNER JOIN tags t ON t.id = tv.tag_id AND t.id = 231014
WHERE v.active = 1 AND v.deleted IS NULL AND v.segment = 1;

Upvotes: 1

Related Questions