SELECT件数を制限してもいい場合のパフォーマンスチューニング

LIMITとGROUP BY / DISTINCT

画面に出すデータ件数が100件だけでいい場合は、LIMIT 100 (OFFSET 0)を使えばいい。
ただLIMITはGROUP BYやDISTINCTを使用すると、条件にあうデータを全件取得して集計したうえで100件だけをクライアントに返すため、パフォーマンスは向上しない。

1:Nのテーブルを結合して、集計関数等を使った上でクライアントに返す時、GROUP BYが必要になることが多いだろう。このような場合、パフォーマンスが悪くなれば、結合をEXISTSに変えて、GROUP BYをやめればLIMITを活用できる。

結合よりもEXISTS

前提

結果セット

(注文ID, 顧客ID, 顧客名, 注文合計金額)

＊注文IDの昇順で上位100件のみ表示すること
＊商品種別は5～9のものに限定すること

LIMITとEXISTSを使ったSQL

SELECT 
注文.注文ID 
, 注文.顧客ID 
, 顧客名 
, (SELECT SUM(価格) FROM 注文商品接続 JOIN 商品 USING(商品CD) 
    WHERE 注文商品接続.注文ID = 注文.注文ID 
  ) AS 注文合計金額 
FROM 注文 
LEFT OUTER JOIN 顧客 USING (顧客ID) 
WHERE EXISTS (SELECT 1 FROM 注文商品接続 JOIN 商品 USING(商品CD) 
              WHERE 注文商品接続.注文ID = 注文.注文ID 
              AND 商品種別 BETWEEN '5' AND '9' 
             ) 
ORDER BY 注文ID 
LIMIT 100

LIMITと結合とGROUP BYを使ったSQL

先ほどのSQLを結合とGROUP BYを使用して書くと次のようになる。

SELECT 
注文.注文ID 
, 注文.顧客ID 
, 顧客名 
, SUM(価格) AS 注文合計金額 
FROM 注文 
JOIN 注文商品接続 USING(注文ID) 
JOIN 商品 USING(商品CD) 
LEFT OUTER JOIN 顧客 USING (顧客ID) 
WHERE 商品種別 BETWEEN '5' AND '9' 
GROUP BY 注文.注文ID, 注文.顧客ID, 顧客名 
ORDER BY 注文.注文ID 
LIMIT 100

結論

結合のGROUP BY禁止！

ORDER BYが入るテーブルをメインとして、1:Nの関係になっているテーブルは結合ではなくEXISTS句にすることで、GROUP BYを消すことがポイントになる。