INNER JOIN 的效能 (SQL)

INNER JOIN 的效能 (SQL)

個人經驗是 JOIN 三張表以上, 效能明顯變慢。可以使用 sub select 來達到相同的目的。
用一個資料表開得不好的例子來說明。
第一種:

SELECT a. * , ch.listname AS chl, cn.listname AS cnl, en.listname AS enl, jp.listname AS jpl, kr.listname AS krl
FROM aa_mainmenu a
INNER JOIN ch_mainmenu ch ON a.sno = ch.a_id
INNER JOIN cn_mainmenu cn ON a.sno = cn.a_id
INNER JOIN en_mainmenu en ON a.sno = en.a_id
INNER JOIN jp_mainmenu jp ON a.sno = jp.a_id
INNER JOIN kr_mainmenu kr ON a.sno = kr.a_id
ORDER BY a.priority DESC


第二種:

SELECT *
FROM (

SELECT a. * , b.listname AS kr
FROM aa_mainmenu a
INNER JOIN kr_mainmenu b ON a.sno = b.a_id
)a
INNER JOIN (

SELECT a. * , b.en, b.jp
FROM (

SELECT a.a_id, a.listname AS ch, b.listname AS cn
FROM ch_mainmenu a
INNER JOIN cn_mainmenu b ON a.a_id = b.a_id
)a
INNER JOIN (

SELECT a.a_id, a.listname AS en, b.listname AS jp
FROM en_mainmenu a
INNER JOIN jp_mainmenu b ON a.a_id = b.a_id
)b ON a.a_id = b.a_id
)b ON a.sno = b.a_id
ORDER BY a.priority DESC


第二種看起來較複雜, 但執行效能通常比第一種好。

引用:http://qops.blogspot.com/2008/01/inner-join-sql.html