一千萬個為什麽

搜索
當前位置: 首頁 > 查詢優化

查詢優化

我有以下表格

chapters
    id
    title
videos
    id
    chapter_id
    video_url
viewed_videos
    id
    member_id
    video_id
    viewed_date

我現在正在使用以下查詢。

select 
    c.id, 
    c.title, 
    c.duration, 
    c.visible, 
    v.id as vid, 
    v.title as video_title, 
    v.chapter_id, 
    v.duration as video_duration, 
    (select count(*) from viewed_videos where video_id = v.id and member_id=32) as viewed
from chapters as c
left join videos as v
on
c.id = v.chapter_id
where
c.tutorial_id = 19

這是使用“查看”字段查詢所有視頻的最佳方式嗎?

我認為,因為我使用子查詢,所以一定比這種方式更好。

最佳答案

你不需要子查詢。您可以在外層進行連接和聚合:

select c.id, c.title, c.duration, c.visible, v.id as vid, v.title as video_title, 
       v.chapter_id, v.duration as video_duration, v.video_token, count(*) as viewed
from chapters as c left join
     videos as v
     on c.id = v.chapter_id left join
     viewed_videos vv
     on vv.video_id = v.id and member_id=32
where c.tutorial_id = 19
group by c.id, v.id;

但是,子查詢並不是一件壞事。事實上,子查詢中的性能比這個版本更有可能。

轉載註明原文: 查詢優化