MySQL 交集的寫法和效率

之前在這篇提到用 union all + group by + having 的方式寫交集, 當時沒測效率。最近剛好有個簡單例子, 比了一下效率。我要從 T1、T2 兩個表格取出欄位 F, 兩個表格各自的資料有重覆的 F, 我想找出 T1、T2 的  F 的共通值。

寫法如下:

1. SELECT DISTINCT F FROM T1 where F in (SELECT DISTINCT F FROM T2)
2. SELECT F, COUNT(*) FROM (SELECT DISTINCT F FROM T1 UNION ALL SELECT DISTINCT F FROM T2) AS t GROUP BY F HAVING COUNT(*) = 2;

T1、T2 各有數萬筆資料。測的結果是第二個寫法瞬殺, 第一個寫法等個十秒沒結果, 我就懶得等了。

原因出在第一種寫法的 sub-query 被當作 dependency query 來處理, 變成每一筆資料都來個 sub-query。不過 MySQL 會有 query cache, 照理說不會慢到這種程度才對, 有機會再去了解詳細運作過程吧。至少藉這機會確定了用 union all 的寫法較好。