# 抽出したテーブル同士をLEFT JOINするやり方

Question

環境はMySQL8.0, Rails6ですが、ActiveRecordの前にSQLを組み立てようとしています。

以下２つのテーブルがあります。base_seatsテーブルでは、会場（hall)にある全座席の名前（name)などの基本情報を、seatsテーブルでは、このbase_seatsテーブルをベースに、イベントで販売する座席を登録しています。

また、base_seatは同じ座席で複数のイベントを開催することになるので、base_seatモデルとseatモデルは、１対多の関係となります。

base_seatsテーブル

id	hall_id	name
1	1	1-1
2	1	1-2
3	1	1-3
4	1	1-4
5	2	1-1
6	2	1-2
7	2	1-3

seatsテーブル

id	event_id	base_seat_id	order_id
1	1	2	1
2	1	3	NULL
3	1	4	2
4	2	2	3
5	2	3	4
6	2	4	NULL

欲しい結果

base_seatsテーブルをhall_id=1で抽出、seatsテーブルをevent_id=1で抽出、両者をleft joinし、注文のついてない座席も取得します。それぞれのテーブルの件数が10万ほどあるので、抽出した結果でないと時間がかかってしまいます。

id	hall_id	name	base_seat_id	event_id	order_id
1	1	1-1	NULL	NULL	NULL
2	1	1-2	2	1	1
3	1	1-3	3	1	NULL
4	1	1-4	4	1	2

やってみたこと

以下2つのsqlをつくってみましたが、どれも30秒以上かかってしまうという結果でした。以下のものは両テーブルをそれぞれ抽出したものを結合するというようにはなっていないのですが、サブクエリ同士を結合するというやり方はWEB検索して見当たりませんでした。

SELECT b.*, s.*
FROM base_seats AS b

LEFT OUTER JOIN seats AS s
ON b.id = s.base_seat_id AND s.event_id = 1

WHERE b.hall_id = 1

SELECT b.*, c.*
FROM base_seats AS b

LEFT OUTER JOIN (
	SELECT *
	FROM seats AS s
	WHERE s.event_id = 1 ) AS c
ON b.id = c.base_seat_id

WHERE b.hall_id = 1

よろしくお願いいたします。

Answer 1

base_seatsテーブルをhall_id=1で抽出、seatsテーブルをevent_id=1で抽出

ところが、SQLは

ON b.id = s.base_seat_id AND s.event_id = 20

WHERE b.hall_id = 16

と矛盾していいるので、質問を修正してください。
質問にCREATE TABLE, CREATE INDEX, EXPLAINによるMySQLの実行計画の確認も載せてください。
テーブル名や列名をバックスラッシュで囲むなら全部のテーブル名や列名を囲んでください。

Comments

[orori]

申し訳ありません、ご指摘の箇所修正しました。実行計画は先ほども指摘いただいたのですが、理解しておらず現在調べております。後ほど結果を報告する予定です。

[orori]

下の方のご指摘のとおり飛躍的に改善しました。また、ご指摘の内容についても、今後は確認するようにいたします。ありがとうございました。

Answer 2

時間が掛かってしまうのは、SQLの組み方よりも適切なインデックスが設定されていない事ではないかと思います。

実行計画にて確認してみて下さい

Comments

[orori]

実行計画について全く理解していませんでした。seatsテーブルのrefがall,rowsが全件数になってましたが、以下を実行することによって、rowは3になり大幅に改善されました。

ALTER TABLE seats
ADD INDEX index_base_seatId(base_seat_id)

実行計画についてさらに学びたいと思います。大変にありがとうございました。

Answer 3

両テーブルをそれぞれ絞り込んだ結果同士でLeft Join したいということですかね。
以下の感じでいかがでしょうか？

SELECT b.*, c.*
FROM (
    SELECT *
    FROM `base_seats`
    WHERE `hall_id` = 16) AS b

LEFT OUTER JOIN (
    SELECT *
    FROM seats AS s
    WHERE s.event_id = 20 ) AS c
ON b.`id` = c.`base_seat_id`

Comments

[orori]

ありがとうございます！FROMにもサブクエリをつけられるのですね！！！早速ためしてみましたが、MacBookProのローカル環境で28秒かかりました。実際は両テーブルとも10万件ほどあるので仕方がないのでしょうか。SQLで結合せず、それぞれ抽出した結果をプログラム内で結合するのがよい場合もあるのでしょうか・・・

[orori]

上の方にも回答しましたが、indexを適切につけていなかったのが遅くなる要員でした。ですが今回は大変にありがとうございました。