Select with inner join TABLE1 OR inner join TABLE2-CodePudding

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
    )
)

CodePudding user response：

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;