左外部結合の出力に長時間かかってしまう

Question

既存のDBをベースにrailsでアプリを作っている初心者です。
RailsなのでActiveRecord+MYSQLなのですが、その前にSQLでどのように表現するかがわかっておりません。

ほしい結果

あるイベントの座席状況として、座席のseatテーブルをベースに、予約のついている座席にだけevent_id, order_idが入るようにしたい。

seat_id	place_id	seat_name	event_id	order_id
1	1	1-1-1	1	1
2	1	1-1-2
3	1	1-1-3	1	2
4	1	1-1-4	1	2
5	1	1-1-5

試したこと

SELECT  seat.*, reservation.* 
FROM seat
LEFT OUTER JOIN reservation
ON seat.id = reservation.seat_id
WHERE reservation.event_id = 1 
OR seat.place_id = 1

上ですが、実際の環境の100万件ほどの予約テーブルと100万件の座席テーブルでは1時間待っても結果が出ませんでした。OR文を削除すると、即座に結果は出力されました。ですが、それですと、空き席の情報が取得できません。

予約のついた席、通常の席、それぞれ出力して、JS内で配列として結合したほうがよいのか、など3日間ほどずっと悩んでいます・・・
何卒、よろしくお願いいたします。

既存の3テーブル

event table

id	event_name	place_id
1	エービーシー	1
2	あいうえイベント	2

seat table

※複数のイベントに利用される、ある会場（place)の予約のない素の座席

id	place_id	seat_name
1	1	1-1-1
2	1	1-1-2
3	1	1-1-3
4	1	1-1-4
5	1	1-1-5
6	2	1-1-1
7	2	1-1-2

reservation table

event_id	seat_id	order_id
1	1	1
1	3	2
1	4	2

Comments

[maisumakun]

EXPLAIN SELECT （以下同じ）として、実行計画は確認してみましたか？

[orori]

下の方で解決はしましたが、以前のSQL文で、EXPLAIN SELECTを行いますと、Using join buffer(Block Nested Loop)と出てましたので、問題となった部分を理解しておきたいと思います。ありがとうございました！

Answer 1

そもそも希望の結果からすると、

WHERE reservation.event_id = 1 
   OR seat.place_id = 1

上記のor条件ではないですね。

ある会場（place_id = 1）の特定のイベント（event_id = 1 ）の状況なので、交わる所はAND条件です。
なので、yambejpさんのSQLが求めるものです。

それでも遅い場合は実行計画でインデックスが適用されているか確認して下さい。
必要なインデックスは、
・seat(id,place_id)
・reservation(seat_id,event_id)
だと思われます。

Comments

[orori]

OR条件ではないこと、おっしゃる通りでした。ありがとうございます。
インデックスの適用について、現在SequelProで217msと表示されていますが、可能な限り早くしたいと考えています。
以下２つのテーブルの実行計画ですが、typeがconstなのでインデックスが適用されている（？）かと思います。ご提案の内容を実行し検討してみます。ありがとうございました！

SIMPLE seat NULL ref place_id place_id 9 const 2688 100.00 NULL
SIMPLE reservation NULL ref PRIMARY PRIMARY 8 const 1490 100.00 Using where

Answer 2

ちゃんと理解していないかもしれませんがこう？

SELECT  seat.*, reservation.* 
FROM seat
LEFT OUTER JOIN reservation
ON seat.id = reservation.seat_id
and reservation.event_id = 1 
where seat.place_id = 1

Comments

[orori]

素晴らしいです！そのまま動きました！！なるほど、外部結合するテーブルをANDで絞り込んで、それをwhereで結合結果全体で絞り込むわけですね・・・ありがとうございました。