データ抽出後にテーブルを結合、さらに絞り込みをするSQL

Question

前提条件

以下のような「articlesテーブル」、「roomsテーブル」、「facilities_traitテーブル」があるとします。

articles.table

id	flag	title
101	2	××アパート
102	1	〇〇マンション
103	2	△マンション
:	:	:

rooms.table

id	flag	status	articles_id
1	2	2	101
2	1	2	101
3	2	2	102
4	1	2	103
5	2	1	103
6	2	1	103
:	:	:	:

facilities_trait.table

id	flag	type	trait_id	facilities_id
1	2	1	101	12
2	2	1	101	30
3	2	2	1	31
4	2	1	102	12
5	2	1	102	30
6	2	1	103	12
7	2	2	3	28
8	2	1	103	30
9	2	2	3	31
10	2	1	104	30
11	2	2	4	31
12	2	3	105	25
13	2	1	105	30
14	2	2	5	31
15	2	2	6	27
16	2	1	106	30

facilities_traitテーブルのtrait_idにはarticles.idとrooms.idが混在していて、typeでarticlesかroomsか判別しています。

実装したいこと

1.
まず、facilities_traitテーブルのfacilities_id = 31 を持つ trait_id を全て取り出します。

facilities_trait.table

id	flag	type	trait_id	facilities_id
3	2	2	1	31
9	2	2	3	31
11	2	2	4	31
14	2	2	5	31

2.
type = 2なのでroomsテーブルを見て、facilities.trait_id = rooms.idで紐づくものを取り出します。

rooms.table

id	flag	status	articles_id
1	2	2	101
3	2	2	102
4	1	2	103
5	2	1	103

その際rooms.flag = 2,rooms.status = 2以外のものは除外します。

rooms.table

id	flag	status	articles_id
1	2	2	101
3	2	2	102

3.
articlesテーブルでrooms.articles_idに紐づくものを取り出します。

articles.table

id	flag	title
101	2	××アパート
102	1	〇〇マンション

その際articles.flag = 2以外のものは除外します。

articles.table

id	flag	title
101	2	××アパート

4.
articles.idをもって改めてfacilitiesテーブルに戻り、articles.id = facilities_trait.trait_idを取り出します。

facilities_trait.table

id	flag	type	trait_id	facilities_id
1	2	1	101	12
2	2	1	101	30

ここで、facilities_traitテーブルのflag = 2 type = 1 OR type = 3の条件に合致するものを抽出したいです。

最終的に欲しい結果

facilities_trait.table

id	flag	type	trait_id	facilities_id
1	2	1	101	12
2	2	1	101	30

現状のコード

分かりやすさを優先させましたが、facilities_trait.trait_idに入るarticlesとroomsのidは被る可能性があります(その為のtypeでもあります)。

SELECT * FROM facilities_trait WHERE flag = 2 AND (type = 1 OR type = 3)
 AND EXISTS( SELECT 'X' FROM articles WHERE flag = 2 AND facilities_trait.trait_id = articles.id
 AND EXISTS( SELECT 'X' FROM rooms WHERE flag = 2 AND status = 2 AND rooms.articles_id = articles.id
 AND EXISTS( SELECT 'X' FROM facilities_trait WHERE rooms.id = facilities_trait.trait_id AND facilities_trait.facilities_id = 31)))

ひとまずは上記のコードで目的の結果は得られておりました。
ただ、クエリがすごく重たいのでINNER JOIN等で書き換えたいと思っております。

皆様の知恵をお借りしたいです。
宜しくお願い致します。

Comments

[K_Y_M]

「実装したいこと」の 2. でtypeが2の場合のみ記載されておりますが、
typeが2以外を取った場合は、どのテーブルのデータと組み合わせるのでしょうか？

[Dammyyy]

すこしややこしいのですが、１，３の場合はarticles、２，４の場合にroomsになります。

[sazi]

> 2.type = 2なのでroomsテーブルを見て、facilities.trait_id = rooms.idで紐づくものを取り出します。
type = 2だけを対象にするという事でOK？
そうじゃないなら、type ＝2以外の場合の条件を追記して下さい。

[Dammyyy]

type = 2だけを対象とするという認識で大丈夫です。
facilities_id = 31 を持つのはtype = 2だけになります。

Answer 1

質問のSQLに間違いがないなら相関がおかしい事になっているので、そのせいで遅いのではないでしょうか。

条件をみると、existsによる相関である必要も無いようですので、素直に記述すると以下の様になると思います。

select * from facilities_trait
where flag = 2 and type in (1, 3) and facilities_id = 31
  and rooms_id in (
        select id from rooms 
        where flag = 2 and status = 2
          and articles_id in (
                select id from articles where flag = 2
              )
      )

それからそれぞれのテーブルに以下のインデックスがあれば、効率が良くなると思います。
facilities_trait=(flag, type, facilities_id, rooms_id)
rooms=(flag, status, articles_id, id)
articles=(flag, id)
※項目の並びは、カーディナリティに合わせて適宜変更が必要です。

追記

※SQLが変更されたので、内容に合わせて追記
素直に記述。

SELECT * FROM facilities_trait 
WHERE flag = 2 AND type in (1, 3) 
  AND trait_id in ( 
        SELECT id FROM articles 
        WHERE flag = 2 
          AND id in ( 
                SELECT articles_id FROM rooms 
                WHERE flag = 2 AND status = 2 
                  AND id in (
                        SELECT trait_id FROM facilities_trait 
                        WHERE facilities_id = 31
                      )
              )
      )

条件内をJoinで。

SELECT * FROM facilities_trait 
WHERE flag = 2 AND type in (1, 3) 
  AND trait_id in ( 
        select articles.id 
        FROM  articles 
              inner join rooms
              on    articles.id = rooms.articles_id
                and articles.flag = 2 and room.flag = 2 AND room.status = 2 
              inner join facilities_trait
              on    rooms.id=facilities_trait.trait_id
                and facilities_trait.facilities_id = 31
      )

Comments

[Dammyyy]

ご回答ありがとうございます。
確認したところ、確かに質問中のSQLは間違っていました。
さらに言えば仕様を理解しきれていなかったようで、さらに複雑なことになりそうです。
詳細を質問の方に追記いたします。

テーブルのインデックスについては失念しておりました。慌てて設定しなおしました。

[Dammyyy]

失礼いたしました。上記のSQLは合っておりました。混乱させてしまい申し訳ありません。

[sazi]

本当ですか？
EXISTS()中にある「r,f」の相関参照先はどこを指していますか。

[Dammyyy]

コピペするSQLを間違っていました。。正しくは別名は使っていませんでした。
`SELECT * FROM facilities_trait WHERE flag = 2 AND (type = 1 OR type = 3) AND EXISTS( SELECT 'X' FROM articles WHERE flag = 2 AND facilities_trait.trait_id = articles.id AND EXISTS( SELECT 'X' FROM rooms WHERE flag = 2 AND status = 2 AND rooms.articles_id = articles.id AND EXISTS( SELECT 'X' FROM facilities_trait WHERE rooms.id = facilities_trait.trait_id AND facilities_trait.facilities_id = 31)))`

[Dammyyy]

求めていた結果が得られました。ありがとうございました。
ベストアンサーにさせて頂きます。

[sazi]

相関が効率的な場合というのは、相関元が条件等で結果的に件数が少ない場合です。
そうでない場合は、inなどの条件にする方が効率的です。

[sazi]

因みに追記したパターンは、どちらでも結果はあまり変わらないと推測していましたが、どうだったでしょうか。

[Dammyyy]

仰る通り、どちらのパターンも速度はほとんど同じでした。
(元のEXISTS文とは比べるべくもなくどちらも高速でした。)

INや相関について、今一度勉強しなおそうと思います。

[sazi]

質問のSQLでは相関なので、facilities_traitを読み込んだ件数分条件のexistsが実行されるのに対して、回答した内容は条件のサブクエリーが一度だけ実行されて、facilities_traitを絞り込むのです。
性能の差は条件のトータル処理時間に起因しています。

Answer 2

以下のSQL文を試してみて下さい

SELECT f.* FROM facilities_trait as f
INNER JOIN facilities_trait as f2
ON f2.rooms_id = f.rooms_id
AND f2.facilities_id = 31
INNER JOIN articles AS a
ON a.id = f.rooms_id
AND a.flag = 2
INNER JOIN rooms AS r
ON r.id = f.rooms_id
AND r.flag = 2 
AND r.status = 2
WHERE f.flag = 2 AND (f.type = 1 OR f.type = 3) 

Comments

[sazi]

条件が変わっていますし、facilities_trait の件数と一致する考慮もなされていないですね。

[K_Y_M]

条件を最後に追加し忘れていた為、追加しました。
＞facilities_trait の件数と一致する考慮もなされていない
これは質問者の方のどの部分を指されているのでしょうか？

[sazi]

サンプルデータは、1:1のようですけど、一意キーが明確に定義されていない状態なので。
明確でない状態では、抽出条件内でjoinを使用しないと、結果が相違する可能性があるからです。