在 MySQL 5 中的多級連接中優化父表上的不同

我有以下表格：在 MySQL 5 數據庫中：

• projects- 1,000 行
• tasks(FKed to projects) - 10,000 行
• task_tags(FKed to tasks) - 350,000 行
• task_comments(FKed to tasks) - 750,000 行

並希望獲得一個項目列表，其中任何任務的標籤或評論中都包含“緊急”一詞，即

select DISTINCT p.*
from projects p
join tasks t on p.id = t.project_id
left join task_tags tt on t.id = tt.task_id
left join task_comments tc on t.id = tc.task_id
where tt.value = 'urgent' OR tc.text = 'urgent'

如果沒有DISTINCT，查詢將在 0.1 秒內執行（並且使用​​的索引看起來很合理）。視覺化執行計劃是（表格為projects、tasks、task_tags並按task_comments從左到右的順序）：

添加DISTINCT會使查詢性能嚴重下降（大約幾分鐘），在最後一個嵌套循環之後添加了 DISTINCT，因此我認為需要 MySQL 對所有結果行進行排序，然後對它們進行重複數據刪除。

雖然這是正確的，但感覺並不是最好的選擇——畢竟，一旦我們知道一個項目符合標準，就沒有必要檢查該項目的任何其他任務，因為這不是例如select distinct p.*, t.id——但我不知道’不知道如何鼓勵 MySQL 更明智地計劃這個，因為我不知道我想要什麼計劃。

我嘗試了一些類似的東西

select distinct project_id
from tasks
where id in (
   select task_id from task_tags where value = 'urgent'
   union
   select task_id from task_comments where text = 'urgent'
)

作為替代方案，但我們也需要幾分鐘的時間，儘管我相信它會稍微快一些，而且我沒有其他好主意（除了 MySQL 5 中沒有的東西，比如持久化視圖，但升級數據庫不是一個實際的選擇在這個階段）。

關於我可以嘗試什麼（甚至下一步在哪裡調查）的任何想法？數據庫模型相當固定，但我當然可以添加索引並樂於查看選項。

讓我們把它翻過來，這樣我們就可以看到它是從正確的地方開始的。優化器不會為我們做這項工作。

1. 從每張可能會說“緊急”的表格開始
2. UNION他們。(UNION DISTINCT比 稍慢UNION ALL，但你可能會得到兩個重複的行。你決定。)
3. 加入即可tasks獲得project_id
4. 最後，進入projects需要的幾行。（請注意，在確定不需要p大多數行之前，您的兩個公式都需要有效地獲取所有行。）

從ORto切換UNION是一個好主意，但IN ( SELECT ... )不是一個有效的結構。

SELECT p.*
   FROM (
        SELECT t.project_id
           FROM task_comments tc
           JOIN tasks t  ON t.id = tc.task_id
           WHERE tc.text = 'urgent'  -- see Note
        ) UNION DISTINCT (
        SELECT t.project_id
           FROM task_tags tt
           JOIN tasks t  ON t.id = tt.task_id
           WHERE tt.value = 'urgent'
        ) AS x
   JOIN projects p  ON p.id = x.project_id

那將需要

tc:  INDEX(text, task_id)  -- see Note
t:   (I assume you have PRIMARY KEY(id))
tt:  INDEX(value, task_id)
p:   (I assume you have PRIMARY KEY(id))

注意：也許您真的想在 中的任何地方檢查“緊急” tc.text？如果是這樣，優化它的最佳方法是擁有

tc:  FULLTEXT(text)

並切換到

WHERE MATCH(tc.text) AGAINST ('+urgent' IN BOOLEAN MODE)
        

引用自：https://dba.stackexchange.com/questions/298538