最近，私はAvionのレスポンス改善についてをよく調査している。
レスポンスとデータベースで実行されるもののスピードに強く関係があるのは常識だと思う。
だから、SQLで効率のよい書き方はプログラムのレスポンスに対して大事なことになった。

今回はSQLの結果では同じ結果を返す、3種類の結合の速度を比べてみた。
今回はサンプルテーブルとして、下記の２つのテーブルを使用する。
user_m;
+———–+————+——+—–+———————+
|Field 　 Type 　　 Null Key Default Extra
+———–+————+——+—–+———————+
|user_id 　 int 　　 NO PRI NULL auto_increment
|name 　varchar(100) YES 　 NULL
|sex 　 bit 　　 NO 　 1
|birth_day datetime 　 NO 　 1970-01-01
|delete_flag bit 　　 NO 　 0
+———–+————+——+—–+———————+

user_buy_list;
+———- —+———– —+——+—–+—————-+
| Field Type Null Key Default Extra
+————–+————–+——+—–+—————-+
| buy_id int NO PRI NULL auto_increment
| user_id int NO FOR -1|
| product_name varchar(100) YES NULL
| price smallint NO 0
| number smallint NO 0

いわゆる、ユーザテーブルとユーザが購入した商品の明細テーブルだ。

ループでランダムテストデータを入れて、user_mテーブルに２００００件、user_buy_list
に６００００件になった。

さて、いよいよ実験をしてみたいとおもう。
ユーザテーブルと明細テーブルの結合SQLを三種類作成した。
取得するデータは「ユーザテーブルの名前、明細の数、金額＊購入した個数の合計」
１．INNER JOINでユーザテーブルと明細テーブルを結合した結果。
２．LEFT JOINでユーザテーブルと明細テーブルを結合した結果。
３．サブクエリでユーザテーブルを絞りこみを行ったデータに対して、INNER JOINで
明細テーブルを結合した結果。

—————————————————————
１．
SELECT
um.name, count( * ) AS jyutyu,
sum(ubl.price * ubl.number) AS total
FROM user_m um
INNER JOIN user_buy_list ubl ON um.user_id=ubl.user_id
WHERE um.birth_day <= ‘1960’ AND um.sex = ‘1’
GROUP BY um.user_id;

**************************************************
4504 rows in set, 1 warning (0.49 sec)

—————————————————————
２．
SELECT
um.name, count( * ) AS jyutyu,
sum(ubl.price * ubl.number) AS total
FROM user_m um
LEFT JOIN user_buy_list ubl ON um.user_id=ubl.user_id
WHERE um.birth_day <= ‘1960’ AND um.sex = ‘1’
GROUP BY um.user_id;

***************************************************
4504 rows in set, 1 warning (0.26 sec)

—————————————————————

３．
SELECT
um.name, count( * ) AS jyutyu,
sum(ubl.price * ubl.number) AS total
FROM
(SELECT *
FROM user_m
WHERE birth_day <= ‘1960’ AND sex = ‘1’ ) as um
INNER JOIN user_buy_list ubl ON um.user_id=ubl.user_id
GROUP BY um.user_id;

***************************************************
4504 rows in set, 1 warning (0.27 sec)
—————————————————————-

１は２や３に比べて、約2倍の処理時間がかかってしまっている。
このように書き方ひとつで、結構な違いが出てくる。

みなさんのお勧めのSQLの書き方がございましたら、ご教授いただけたら幸いです。