MySQLでの取得が複雑な条件で困っています

Question

【前提・実現したいこと】

MySQL5.7 にて、以下の【条件】でpostsテーブルから取得ができました。

しかし、【現状のコード】はWHEREの対象がpostsのすべてになっていると思います。

そこで、➀➁➂➃に限定したpostsを対象に、➄➅でWHEREをかけることができれば早くなると思っているのですが、そのための【試したコード】で躓いています。

###【条件】
★ 取得条件
➀post_kind = 1 のもの
AND
指定ユーザが ➁投稿したもの OR ➂フォローしたもの OR ➃保存したもの

☆ 除外条件
➄削除されたもの（ is_trash=1 のもの ）
OR
➅ブロックした、されたユーザによるもの

【CREATE、INSERT】

ユーザ、投稿、アクション（フォロー、保存、ブロック）
という５つのテーブルで構成されます。

-- ユーザ
CREATE TABLE users(
  `ID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`ID`)
);
INSERT INTO users
    (`name`)
VALUES
    ('A子'),('B子'),('C子'),('D子'),('E子')
;

-- 投稿
CREATE TABLE posts(
  `ID` INT NOT NULL AUTO_INCREMENT,
  `user_ID` INT NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `post_kind` VARCHAR(10) NOT NULL,
  `is_trash` INT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  INDEX (user_ID),  
  CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`)
);
INSERT INTO posts
    (`user_ID`, `title`, `post_kind`)
VALUES
    (4, 'a', 1), (2, 'b', 1), (2, 'c', 1), (3, 'd', 1), (1, 'e', 1),
    (5, 'd', 2), (2, 'f', 2), (5, 'g', 2), (2, 'h', 2), (1, 'j', 2)
;

-- 保存した投稿
CREATE TABLE keeps(
  `user_ID` INT NOT NULL,
  `post_ID` INT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_ID`,`post_ID`),
  INDEX (`user_ID`,`post_ID`),  
  CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`),
  CONSTRAINT FOREIGN KEY (`post_ID`) REFERENCES posts(`ID`)  
);
INSERT INTO keeps
    (`user_ID`,`post_ID`)
VALUES
    (3,1),(2,4),(2,1),(1,1),(4,1),(2,2),(3,5),(5,1),(3,2),(4,5)
;

-- フォローした投稿
CREATE TABLE follows(
  `user_ID` INT NOT NULL,
  `post_ID` INT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_ID`,`post_ID`),
  INDEX (`user_ID`,`post_ID`),
  CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`),
  CONSTRAINT FOREIGN KEY (`post_ID`) REFERENCES posts(`ID`)
);
INSERT INTO follows
    (`user_ID`,`post_ID`)
VALUES
    (5,3),(4,4),(2,2),(2,3),(1,4),(2,5),(5,1),(2,4),(5,4),(3,1)
;

-- ブロックしたユーザ
CREATE TABLE blocks(
  `user_ID` INT NOT NULL, -- した側
  `user_ID2` INT NOT NULL, -- された側
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_ID`,`user_ID2`),
  INDEX (`user_ID2`,`user_ID`),
  CONSTRAINT FOREIGN KEY (`user_ID`) REFERENCES users(`ID`),
  CONSTRAINT FOREIGN KEY (`user_ID2`) REFERENCES users(`ID`)  
);
INSERT INTO blocks
    (`user_ID`,`user_ID2`)
VALUES
    (1,5),(1,2),(3,4),(5,1),(4,1)
;

###【現状のコード】
このデータから条件に合致した投稿を得るSELECTですが、先述のようにWHEREの対象がpostsのすべてになっていると思います。

SELECT
  p1.ID post_id,
  CASE WHEN p1.post_kind = 1 THEN 'dog' WHEN p1.post_kind = 2 THEN 'cat' END post_kind,
  k.created_at date_keeping,
  f.created_at date_following,
  p2.created_at date_posting,
  u.ID author_id,  
  u.name author_name,
  -- 以下2つのblock日はSELECTに不要でした ( 2020/08/15 14:21 に削除 )  
  -- b1.created_at date_blocking,
  -- b2.created_at date_blocked
  
FROM
  posts p1

  -- 投稿者情報を連結
  LEFT JOIN users u ON u.ID = p1.user_ID 
  
  -- 2(B子) のブロック情報を連結
  LEFT JOIN blocks b1 ON b1.user_ID = 2 AND b1.user_ID2 = p1.user_ID   
  LEFT JOIN blocks b2 ON b2.user_ID2 = 2 AND b2.user_ID = p1.user_ID   
  
  -- 2(B子) の keep 情報を連結
  LEFT JOIN keeps k ON k.user_ID = 2 AND k.post_ID = p1.ID

  -- 2(B子) の follow 情報を連結
  LEFT JOIN follows f ON f.user_ID = 2 AND f.post_ID = p1.ID

  -- 2(B子) の 投稿 情報を連結
  LEFT JOIN posts p2 ON p2.user_ID = 2 AND p2.ID = p1.ID

-- 以下のWHEREは間違いです ( 2020/08/15 14:07 に削除 ) 
/*
WHERE
  p1.post_kind = 1 -- ➀
  AND p1.is_trash = 0 -- ➄
  AND b1.created_at IS NULL -- ➅
  AND b2.created_at IS NULL -- ➅
  -- 以下忘れていたので追記( 2020/08/14 13:34 に追加 )
  AND ( k.created_at IS NOT NULL OR f.created_at IS NOT NULL ) -- ➂➃
*/

-- 以下のWHEREが正しいです ( 2020/08/15 14:07 に追加 ) 
WHERE
  p1.post_kind = 1 -- ➀
  AND p1.is_trash = 0 -- ➄
  AND (
    (
      b1.created_at IS NULL -- ➅
      AND b2.created_at IS NULL -- ➅
      AND ( k.created_at IS NOT NULL OR f.created_at IS NOT NULL ) -- ➂➃
    )
    OR p2.created_at IS NOT NULL -- ➁
  )

【試したコード】

➀➁➂➃に限定したpostsを対象に、➃➄でWHEREをかけようと思い、以下、下の３つでINNSER JOINを使ったのですが、これは連続では使えないようで、どうすればいいかわからず躓いています。

FROM
  posts p1

  -- 投稿者情報を連結
  LEFT JOIN users u ON u.ID = p1.user_ID 
  
  -- 2(B子) のブロック情報を連結
  LEFT JOIN blocks b1 ON b1.user_ID = 2 AND b1.user_ID2 = p1.user_ID   
  LEFT JOIN blocks b2 ON b2.user_ID2 = 2 AND b2.user_ID = p1.user_ID   
  
  -- 2(B子) の keep したものに限定
  INNER JOIN keeps k ON k.user_ID = 2 AND k.post_ID = p1.ID

  -- 2(B子) の follow したものに限定
  INNER JOIN follows f ON f.user_ID = 2 AND f.post_ID = p1.ID

  -- 2(B子) の 投稿 したものに限定
  INNER JOIN posts p2 ON p2.user_ID = 2 AND p2.ID = p1.ID

自分なりの考えで【試したコード】の書き方になりましたが、INNER JOINよりも良い方法などあればぜひ知りたいです。

とにかく最適な取得は何かについて勉強されていただければと思っていますので、ご回答宜しくお願い申し上げます。

Comments

[yambejp]

条件を文書でただしく説明してくれないとやりようがないですね
テーブル構成もこれがマストなのでしょうか？

[sazi]

> 下の３つでINNSER JOINを使ったのですが、これは連続では使えないようで
とはどういう事ですか？
エラーではないようですし。

[azagomarashi]

yambejp様
テーブル構成もマストかわかりませんが、ひとまずこれで進めようと思っています。明らかにおかしいところがあればご指摘頂けると嬉しいです。
条件の文書はどこか不明瞭だったり、多義的な解釈ができたりしますか？

[azagomarashi]

sazi様
エラーではないのですが、
”【現状のコード】と同じ結果にならない”
ことを指して
”連続では使えないようで”
と書きました。

Answer 1

色々おかしなところがあります。
先ずテーブルの定義において、keepsテーブルとして独立させる意味はありますか？
postsはidで一意なのだから、保存した日を持たせるにしてもpostsに持てば済む話だと思います。

次にSQLについてですが、postsに対してblocksやfollowsは1:Nの関係ですが、joinするとpostsは複数件になりますが、そのような結果を望んでいるのでしょうか？
post_idにつき１件としたいなら、blocksやfollowsは集計したものと結合するか、サブクエリーで１件だけ取得するようにしなければなりません。

postsの自己結合については意味不明です。一意キー同士を結合しても同じ情報しか所得出来ないのでその事に意味がありません。

チューニングの前に、それらを整理する必要があると思います。
あなたの質問はぱっと見で性能を改善したいのが目的のように見えますが、本当は意図通りに抽出できていない事に対してではありませんか？
もしそうなら、もっと要件が分かるように、希望する結果をサンプルとして質問に追記して下さい。

追記

★ 取得条件
➀post_kind = 1 のもの
AND
指定ユーザが ➁投稿したもの OR ➂フォローしたもの OR ➃保存したもの

☆ 除外条件
➄削除されたもの（ is_trash=1 のもの ）
OR
➅ブロックした、されたユーザによるもの

最終形が良く分からないので、上記条件に合わせてpostsを取得するとすると以下の様な記述になります。

select * from posts
where post_kind = 1     -- ①
  and (
          user_id = 2   -- ②
       or id in (
              select post_id from follows where user_id=2 -- ③
            union all
              select post_id from keeps where user_id=2   -- ④
          )
      )
  and is_trash = 0      -- ⑤
  and user_id not in (  -- ⑥
          select user_id from blocks where user_ID2=2
        union all
          select user_id2 from blocks where user_ID=2
      )

これを結合条件に直したとしても、質問に記載されたSQLにはなりません。
なので、質問にあるSQLで本当に得たい結果になっているかどうかが疑問なのです。

Comments

[azagomarashi]

大変多くのアドバイスをありがとうございます！！設計を見直すきっかけになりました。

以下４点に引用コメントさせて頂きましたので、長文で誠にすみませんが…お時間ございますときにご返信いただけますと幸いです。

[azagomarashi]

【1】
> 先ずテーブルの定義において
postsのレコードに「このレコードのIDをkeepしたuser_IDを持たせる」わけですか？うーん。例えばposts_ID=1をkeepしているのはB子だけでなく全員ですが、これをどうpostsにINSERTしますか？
・５つのカラムをレコードに追加する（横に追加）
・または５件のレコードを追加しpostsのPKをなくす（縦に追加）
になると思いますが、いずれもkeepsを独立させることと比べてどんなメリットがあるのかわからないのですが…

【2】
> 次にSQLについて
仰る通り、joinでは1：多は望んでいません。しかしuser_IDを2（B子）と指定すれば、postsに対してblocksやfollowsは1：1になると思いますので、問題視していません。どのようなケースを問題視なさっていますか？（joinが1：多になるケースとは？）

【3】
> postsの自己結合について
自己結合は「> 指定ユーザが ➁投稿したもの OR ➂フォローしたもの OR ➃保存したもの」に走査対象を限定したかったためです。まず「> ➁投稿したもの」に限定し、そのうえでWHEREをかけた方が、全体のpostsにWHEREをかけるより良さそうだと考えた、というのが質問の経緯でしたので。（その考えがあやしいというのはmaisumakun様からご指摘頂きましたが、結局どうすべきかはまだ不明です。）

【4】
> 本当は意図通りに抽出できていない事に対してではありませんか？
一行だけ追記しましたが、【現状のコード】で意図通りに抽出できています。他のサンプルならば、「2（B子）」の部分を「3（C子）」に変更していただければと思います。

[sazi]

【1】これはkeepの目的を取り違え（保存という言葉から投稿を確定した場合の情報だと解釈）ていたので無視して下さい。
【2】postsが軸ではなく、userが軸という事みたいなので無視して下さい。
【3】意図は理解しました。ですがその意図どおりのSQLにはなっていないと思いますよ。
関係しているユーザーに紐つくpostならfollowsとかkeepsのuser_id経由だと思います。
【4】それなら、サンプルデータがパターン網羅できていないのでしょう。
そもそもテーブルの目的すら説明されていないのですから、サンプルデータなり、説明なりでもっと補完がが必要です。

[azagomarashi]

以下たびたび恐れ入りますが引用コメントさせて頂きます。特に【3】が気になります。

【3】
> 関係しているユーザーに紐つくpostならfollowsとかkeepsのuser_id経由だと思います。
こちら読解できませんでした…
おそらく【現状のコード】を『そのように』書き換えるべきだと仰っているのかと思いますが、『どのように」かわからないです。『followsとかkeepsのuser_id経由』といいますと…？JOINがおかしいということですか？

【4-1】
> それなら、サンプルデータがパターン網羅できていないのでしょう
こちらもわからないです。サンプルデータのパターン網羅とは、なんですか？
「B子」を「C子」や「D子」に変更すること（FROMの「user_ID = 2」を「user_ID = 3」や「user_ID = 4」に変更すること）以外に、サンプルの提示ができないのですが…網羅して提示するというのは、まさかそれを「user_ID = 10」まで質問文に書くべきだと仰っているのではないですよね？

【4-2】
> そもそもテーブルの目的すら説明されていないのですから
CREATE文の上のコメントが、テーブルの目的になります。

例えば

-- ユーザ
CREATE TABLE users(
);

とあれば「users」は「ユーザ」のリストですし、

また例えば

-- フォローした投稿
CREATE TABLE follows(
);

とあれば「follows」は「フォローした投稿」のリストです。

[sazi]

【4】
keepはどうですか？
説明を聞くと、投稿者以外が引用した投稿が本来の意味のように受け取れますが、「保存した投稿」で説明になっていると思われますか？
まあ、そこが議論する所では無くて、事実伝わらなかったという事だけです。
SQLが正しいなら通常はそれで補完するのですが、SQLが？？な状態なので。

【3】
> LEFT JOIN posts p2 ON p2.user_ID = 2 AND p2.ID = p1.ID
上記の「user_ID = 2 」は投稿者を指しているのではないですか？
フォロー者の投稿を取得するなら2では無いはずだし、そもそも[p2.ID = p1.ID]の条件はおかしいと思うのですが、違ってますか？
left joinなので抽出の条件には影響していませんが、取得できた場合は指定ユーザーが投稿者の場合のみですので、同じ情報を２重に取得しているだけです。

[azagomarashi]

たしかに、提示したSQLは条件を満たしていませんでした。
user_ID=3で試すと、3が投稿したpost_ID=4が取れませんでした。

ご回答いただいたSQLが（IDの大文字とコメントのスペースを微調整すれば）正しかったです。

まさに「>本当は意図通りに抽出できていない」がはじめっから正しかったわけで、会わせる顔がございません。素人が余計なSQLを提示すべきではなかったと反省しています。ご迷惑おかけして大変申し訳ございませんでした。

[azagomarashi]

コメントに誤りがあったため削除しました

[sazi]

いえ、SQLを提示されるのは良い事なのです。
それにあわせて、テーブルの説明や要求事項が明確である事で、今回はそこに不足があると感じました。

[azagomarashi]

で【3】の「>同じ情報」がなぜあるかというと、それが「>3が投稿したpost_ID=4が取れませんでした」を取るためのJOINです。
つまり以下最後のORで、3の投稿を取得するためのJOINが、【3】の「>同じ情報」です。
なので【現状のコード】のWHEREはこれで提示すべきでした。

WHERE
p1.post_kind = 1
AND p1.is_trash = 0
AND (
(
b1.created_at IS NULL
AND b2.created_at IS NULL
AND ( k.created_at IS NOT NULL OR f.created_at IS NOT NULL )
)
-- 当人の投稿をここで指定する
OR p2.created_at IS NOT NULL
)

[sazi]

> user_ID=3で試すと、3が投稿したpost_ID=4が取れませんでした。
予めデータのパターンをサンプルとして用意しておけば気付けたはずです。
それがテストデータとしてのパターンが網羅出来ていないという事です。

[azagomarashi]

もうまったく仰る通りで..（ ;´꒳`;）

[sazi]

SQLを組み立てるとき、結合条件と抽出条件は分けて考える方が良いですよ。
from句ではあくまで結合条件によって求める集合を作り出し、それをwhere条件でフィルターする。
チューニングするにしても、そこをスタートにする方が明確にできます。

[azagomarashi]

なるほど。つまり質問の【試したこと】は、求める集合を作る前に、INNSER JOINによってその集合を少なくしたら早くなりそうだという考えに基づいたSQLですが、それよりも【現状のコード】のようにまず求める集合を作った方がいいだろいうことですね。

そうしますと、初めのご回答からご指摘頂いていた【3】の自己結合の部分ですが、これはもっと良い方法などあるのでしょうか？こうしないと求める集合から➁が得られないのではと思うのですが、ご指摘に鑑みれば何やら他の適した方法がありそうなにおいがします。よろしければそのお心をお聞かせ願えませんでしょうか。

[sazi]

今回結合した形のSQLで回答しなかったのは、select項目の目的が分からないためです。
条件でブロックされているものは除外するのに、selectではそのデータの日付を取得する矛盾があったり。

[sazi]

現状の質問はSQLで正しく取得できている前提だったのでしょうから、求めたい結果に関する情報が不足しています。
ほとんどこの回答をした時に言っている事ですが、そこを整理して下さい。

[azagomarashi]

たしかにblock日はSELECTに不要でした。間違いばかりで申し訳ございませんでした。

[sazi]

結合条件と抽出条件は分けて考える方が良いと言っているのは、集合が明確になるからです。
これ以上なく正規化されている状態であれば、from句内でインラインビューを使用することはありません。
逆に言うと、インラインビューにしなければ集合全体が表わせないなら、正規化の余地があったりするわけです。

Answer 2

しかし、【現状のコード】はWHEREの対象がpostsのすべてになっていると思います。

そこで、➀➁➂➃に限定したpostsを対象に、➄➅でWHEREをかけることができれば早くなると思っているのですが、

机上の空論で考える前にEXPLAINを走らせましょう。エンジンの方で、絞り込みがより効く条件を優先して使っていることもありえます。

Comments

[azagomarashi]

EXPLAINをうっかりしていたのは申し訳ございませんが、「EXPLAINすればわかる」と間違いだけをあげつらわれてしまうと…
質問はその先についてでして、とにかく最適な取得は何かについて勉強されていただければと思っていますので（ ;´꒳`;）

[maisumakun]

> 「EXPLAINすればわかる」と間違いだけをあげつらわれてしまうと…

いえ、間違いではなく、「それがチューニングの本質」だと考えています。

細かい制御の効かないSQL経由で操らないといけない関係上、実際にコードを書いてEXPLAINして、の繰り返しが最善の手段だと考えています。

[maisumakun]

（元のコードと試したコードで、どちらがいい結果となっていますか？）

[maisumakun]

> 最適な取得は何か

データ分布や分量にすら依存しますので、実際に動かしたデータを見なければ何も言えません。

[azagomarashi]

なるほどですね。「チューニングの本質」ありがとうございます。心得ておきます。