MySQLで「タグ同士の関連性」をデータベースに持たせる方法

Question

前提

phpとMySQLでユーザーがタグを付けてコメントを投稿するシステムを作りました。

今回は 「指定タグの関連タグを取得する目的」 で、「タグ同士の関連性」テーブルを作ろうとしています。

実現したいこと

「タグ同士の関連性」テーブルにおいて、適切なプランを知りたいです。

発生している問題

下記プランＡ、Ｂを考えていますが、経験が浅くどちらを採用すべきか判断ができません。
どういうケースでどういうメリット・デメリットがあるのか、アドバイスを頂きたく思っております。

「コメントとタグ」テーブル（決定）

まず「コメントとタグ」は３つのテーブルが担い、次のstepでそれぞれへINSERTされます。

step1. ユーザーがコメントにタグをつけてPOSTする。
step2. コメントが tbl_comments へINSERTされる。
step3. タグは tbl_tags へINSERTされる。
step4. タグとコメントの紐付けは tbl_tag_holders へINSERTされる。

-- コメント
CREATE TABLE tbl_comments (
  `ID` int AUTO_INCREMENT, 
  `comment` varchar(100) not null, 
  PRIMARY KEY(`ID`),
  INDEX idx_comments_01 (`comment`)); 

INSERT INTO tbl_comments (`ID`,`comment`)
VALUES
(1,  'こんにちは'),
(2,  'ありがとう'),
(3,  'さようなら');

-- タグ
CREATE TABLE tbl_tags (
  `ID` int AUTO_INCREMENT, 
  `tag_kind_id` int, 
  `tag_name` varchar(100),
   PRIMARY KEY(`ID`),
   UNIQUE u_tags_01 (`tag_kind_id`, `tag_name`));
  
INSERT INTO tbl_tags (`ID`, `tag_kind_id`, `tag_name`) 
VALUES -- tag_kind_id は「本、著者、ジャンル」の３種類があります。
(1,  1,  '吾輩は猫である'),
(2,  2,  '夏目漱石'),
(3,  3,  '恋'),
(4,  3,  '人生'),
(5,  1,  '人間失格'),
(6,  2,  '太宰治'),
(7,  3,  '罪');

-- リレーション
CREATE TABLE tbl_tag_holders (
  `comments_ID` int,
  `tags_ID` int,
  PRIMARY KEY  (`comments_ID`, `tags_ID`),
  INDEX idx_tag_holders_01 (`tags_ID`, `comments_ID`), 
  CONSTRAINT fk_tag_holders_01 FOREIGN KEY (`comments_ID`) REFERENCES tbl_comments(`ID`),
  CONSTRAINT fk_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES tbl_tags(`ID`) );
  
INSERT INTO tbl_tag_holders
(`comments_ID`, `tags_ID`)
VALUES
(1, 1),(1, 2),(1, 3),(1, 4),
(2, 5),(2, 6),(2, 7),
(3, 1),(3, 2),(3, 4);

そしてこれに続くstepとして

step5. タグ同士に「タグ同士の関連性」を持たせるべく、tbl_tag_relations へINSERTされる。

があるわで、ここにおいて以下プランで悩んでいる次第です。

蛇足

質問と直接関係ありませんが、例えば 'さようなら' のコメントを次のように検索します。

SELECT C.ID comment_id,
       GROUP_CONCAT( T.tag_name ) tag_names
FROM tbl_comments C
     LEFT JOIN tbl_tag_holders TH ON TH.comments_ID = C.ID
     LEFT JOIN tbl_tags T ON T.ID = TH.tags_ID
WHERE C.comment = 'さようなら'
GROUP BY comment_id;

閑話休題。

「タグ同士の関連性」テーブル（プランＡ）

まず一方向に関連性を持たせ、tag_uniqueカラムでユニーク性を持たせるプランです。

-- タグ同士の関連性
CREATE TABLE tbl_tag_relations (
  `tags_ID1` int,
  `tags_ID2` int,
  `tag_unique` varchar(100) as (concat(least(`tags_ID1`,`tags_ID2`),',',greatest(`tags_ID1`,`tags_ID2`))),
  `count_relations` int not null default 1,
  CONSTRAINT fk_tag_relations_01 FOREIGN KEY (`tags_ID1`) REFERENCES tbl_tags(`ID`),
  CONSTRAINT fk_tag_relations_02 FOREIGN KEY (`tags_ID2`) REFERENCES tbl_tags(`ID`) );

このプランＡですと、例えばタグを３つ持っている 'さようなら' のコメントは、次の３レコードで済みます。

INSERT INTO tbl_tag_relations
(`tags_ID1`, `tags_ID2`)
VALUES
(1, 3),(2, 3),(3, 4);

「タグ同士の関連性」テーブル（プランＢ）

続いて双方向の関連性を持たせるプランです。

-- タグ同士の関連性
CREATE TABLE tbl_tag_relations (
  `tags_ID1` int,
  `tags_ID2` int,
  `count_relations` int not null default 1,
  CONSTRAINT fk_tag_relations_01 FOREIGN KEY (`tags_ID1`) REFERENCES tbl_tags(`ID`),
  CONSTRAINT fk_tag_relations_02 FOREIGN KEY (`tags_ID2`) REFERENCES tbl_tags(`ID`) );

このプランＢですと、'さようなら' のコメントは次の６レコードが必要となります。

INSERT INTO tbl_tag_relations
(`tags_ID1`, `tags_ID2`)
VALUES
(1, 3),(3, 1),(2, 3),(3, 2),(3, 4),(4, 3);

メリット・デメリット

上記プランＡ、Ｂについて 「指定タグの関連タグを取得する目的」 を基に愚見を申しますと、

まず 'さようなら' のコメントの例で述べたように、プランＡの方がtbl_tag_relationsのレコード数が少なくて済む点で良いのかなと思います。

他方SELECTに目を向けてみますと、例えば $target_tag_id=4 と関連するタグを取得するにあたっては、次のコードの違いがあるように思われます。

すなわち、プランＡは tags_ID1 と tags_ID2 のそれぞれにWHEREをかけないといけませんが、プランＢは tags_ID1 に対してだけで済みますので、この点ではＢが良いいのかなと思います。

-- 指定タグの関連タグを取得する（プランＡ）
SELECT * 
FROM (
    SELECT *
    FROM   tbl_tag_relations TR
           LEFT JOIN tbl_tags T 
                  ON T.ID = TR.tags_ID1

    -- tags_ID1 にWHERE
    WHERE  TR.tags_ID1 = $target_tag_id

    GROUP BY TR.count_relations
    ORDER BY TR.count_relations DESC
    LIMIT 10
) UNION (
    SELECT *
    FROM   tbl_tag_relations TR
           LEFT JOIN tbl_tags T 
                  ON T.ID = TR.tags_ID2

    -- tags_ID2 にWHERE
    WHERE  TR.tags_ID2 = $target_tag_id

    GROUP BY TR.count_relations
    ORDER BY TR.count_relations DESC
    LIMIT 10
)
LIMIT 10

-- 指定タグの関連タグを取得する（プランＢ）
SELECT *
FROM   tbl_tag_relations TR
       LEFT JOIN tbl_tags T 
	          ON T.ID = TR.tags_ID1

-- tags_ID1 に対してだけで済みます
WHERE  TR.tags_ID1 = $target_tag_id

GROUP BY TR.count_relations
ORDER BY TR.count_relations DESC
LIMIT 10

このようにメリット・デメリットがよく掴み切れず、どちらが良いのかイマイチ整理がつきません。

ツールのバージョンなど

それぞれ次のバージョンです。

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

Comments

[yambejp]

いまいち理解ができてないのですが
tbl_tag_relationsのA案は単純にtbl_tag_holders のことではないでしょうか？
あ、ちがうか。コメントを無視して任意に特定タグ同士を関連付けるのでしょうか
その場合、1と3、2と3が関連付けられているのに1と2は関連しなくてよいということ？

[nikuatsu]

> tbl_tag_relationsのA案は単純にtbl_tag_holders のことではないでしょうか

たしかに tbl_tag_holders から導くことができる値だとは思います。しかし例えば「tag_id=4と関連性の高いタグを順で（count_relations DESC 順で）取得しよう」となったとき tbl_tag_holders に対しどうSELECTをかけたらいいのか…かなり大変そうに思えます。

あるタグページを閲覧した際に「そのタグと関連性の高いタグはこちら」と表示したいと思っていて、その際のSELECTをかけるのがやりやすいかと思い、tbl_tag_relations を作りました。

> コメントを無視して任意に特定タグ同士を関連付けるのでしょうか

いえ。その機能は考えておらず、（というか）タグ同士を関連付ける適当な方法が思いつかず、ひとまず次のルールにしてみました。

「step1でコメントに付けられたタグが、その場合のみタグ同士の関連性を持つ」です。

> その場合、1と3、2と3が関連付けられているのに1と2は関連しなくてよいということ？

はい、上述のルールに従い、1と2は関連させません。あるコメントに1と2が付けられた場合のみ、1と2は関連させるつもりです。

Answer 1

認識が違ったらすみません。2個のtag同士を限定的に関連性をもたせるならこんな感じとか

create table tbl_tag_relations(
relation_id int not null,
num enum('1','2') not null,
tag_id int,
primary key(relation_id,num),
unique(relation_id,tag_id));

insert into tbl_tag_relations values
(1,'1',1),(1,'2',3),(2,'1',2),(2,'2',3),(3,'1',3),(3,'2',4);

※テーブルのカラム設定とデータ投入を調整しました

テスト

以下でテストしてみて

select * from tbl_tag_relations as t1
inner join tbl_tag_relations as t2
on t1.relation_id=t2.relation_id
and t1.num<>t2.num

こうなりますか？

relation_id	num	tag_id	relation_id	num	tag_id
1	1	1	1	2	3
1	2	3	1	1	1
2	1	2	2	2	3
2	2	3	2	1	2
3	1	3	3	2	4
3	2	4	3	1	3

検索

その上で、1と関連するtag_idを検索

select t1.tag_id from tbl_tag_relations as t1
inner join tbl_tag_relations as t2
on t1.relation_id=t2.relation_id
and t1.num<>t2.num
and t2.tag_id=1;

tag_id
3

Comments

[nikuatsu]

ありがとうございます。

誠に恐れ入りますが、「指定タグの関連タグを取得する目的」のためのselectにおいて、そちらのcreateですとインデックスがないので時間がかかるのでは？という疑問がございます。

例えば insert に (1,'1',1),(1,'2',3) とありこれは「1と3の関連付け」の意味ですよね。ではこれに対し「1と関連するタグ」として「3」を、また逆に「3と関連するタグ」として「1」を見つけるには、どのようなselectになりますでしょうか？

質問の両プランでは fk_tag_relations_01 と fk_tag_relations_02 という外部キーがインデックスとなり、上記目的のselectにおける時間は気にしなくていいと考えているので、恐れながらこの点疑問に思いまして…

[nikuatsu]

あ、もしかして、
unique(relation_id,tag_id) ではなく
unique(tag_id,relation_id) でしたか？

後者であれば上記目的のselectにおけるインデックスとして活用でき時間への懸念はなさそうかなと…

[nikuatsu]

追記ありがとうございます。そちら実行させて頂きましたが、いずれも返り値が空でした。

尚、and t2.tag_id=1 の方のselectですが、and t1.num<>t2.num の部分をなくすと 1,3 が返るので、恐らくこの部分に原因がありそうですが…

そして、and t2.tag_id=1 の指定ですが、やはりご回答のcreateに対してインデックスが効かないのではないでしょうか？

ご回答は次の２つのインデックスが貼られており、tag_id での指定で使うのは後者だと思います。しかし tag_id がその先頭にないので tag_id で指定しても使えないように思えます。
primary key(relation_id,num)
unique(relation_id,tag_id)

誠に恐れながら…、やはりここは unique(tag_id,relation_id) という順序で貼るべきではないのでしょうか？

[nikuatsu]

あ、でもそうしたら、on t1.relation_id=t2.relation_id のときのインデックスがなくなりますね。
ということは、別途 (tag_id,relation_id) という順序のインデックスを貼る必要があるのではないでしょうか。

[yambejp]

ちょっと状況がわかりかねます。
コピペをするだけなので、なぜ「返り値が空」なのか理解できません。
なにか勝手な解釈をいれてクエリーを変更していませんか？

> やはりここは unique(tag_id,relation_id) という順序で貼るべきではないのでしょうか？

ちょっと質問の意図がわかりかねます
順番ってなんでしょう？unique(relation_id,tag_id)とは
relation_idとtag_idの組み合わせがユニークだという制限であって
順番などまったく関係ありませんが？

[nikuatsu]

ご返信ありがとうございます。お手数おかけしております。

> なにか勝手な解釈をいれてクエリーを変更していませんか？
ご回答にある insert into の (3,2,4) だけ (3,'2',4) にしていますが、これはすべきですよね？
そして今改めて全てDROP TABLEし、次のリンクのように完璧にコピペでcreateからselectまで実行しましたが、やはり返り値が空でした。
https://imgur.com/a/lJuALfk

> 順番などまったく関係ありませんが？
これは知らなかったです…！( relation_id,tag_id ) とインデックスを作れば、WHERE relation_id=? ならインデックスが効くけど、WHERE tag_id =? なら効かない。というものだと思っておりました。

[yambejp]

> (3,2,4) だけ (3,'2',4) にしていますが、これはすべきですよね？

失礼しました。ご指摘のとおりです。
（一応数値でもインデックスとして有効なんですけど統一感がないのは問題ですね）

> createからselectまで実行しましたが、やはり返り値が空でした

さまざまなMySQLのバージョンで試しましたが、返り値は確実にありますね
デバグ用のクエリも追記したので試してください

[nikuatsu]

> 以下でテストしてみて
> こうなりますか？
なりました。

> その上で、1と関連するtag_idを検索
検索できました。

そして、原因がわかりました。もう大変申し訳ございません。
コメントアウトの /* 3 */ を書いたまま実行した場合に返り値が空になるようでした。
何も考えずにコピペしてしまったせいで大変なお手数をおかけしてしまいました。

[yambejp]

> コメントアウトの /* 3 */ を書いたまま

失礼しました。それは私の書き方がわるかったです

[nikuatsu]

teratailのコードブロックはコメントアウトとして無効なら赤字にするみたいに、もうちょっとチェックしてほしいですね（人のせいｗ）

ちなみに、プランＢと比べてどの点で良くなるのでしょうか？
プランＡはレコード数が少なくて済む点でプランＢより良さそうだと質問で述べておりますが、ではプランＢとご回答のcreateではどのような違いがあるのかと思いまして。

[yambejp]

(1,3)と(3,1)のように裏返しのデータをもつこと自体が管理が煩雑になるだけで
いいことがひとつもありません。
tags_ID1とtags_ID2は本来同じカラムなのでカラムを分けることが非効率です。

私のサンプルですとnumという順番を持っているようにも見えますが、
これはrelation_idごとにデータを2つ持つというユニークを担保するための
データでしか無いので、順序が不動ですみます。

総じてデータ管理方法としては私のやり方が効率的だと思います

[nikuatsu]

丁寧なご返信をどうもありがとうございます。さっそくそのようにさせて頂きます。

[nikuatsu]

お世話になっております。

ふと思ったのですが、こちら「関連付けの件数」として count_relations カラムを設けるとしたら、tbl_tag_relations テーブルにしますか？それとも別途テーブルを作成されますか？

実は質問の「ORDER BY」が「count_relations DESC」となっておりますのは、「関連付けの件数」が多い順での取得を想定しているためです。

ご回答頂きました tbl_tag_relations テーブルは１つの関連付けに２行を使っていますので、もし tbl_tag_relations テーブルに count_relations カラムを設けるとしたら、次のように「関連付けの件数」が同じ値になるかと思います。

insert into tbl_tag_relations values
(1,'1',1, '関連付けの件数'), (1,'2',3, '関連付けの件数'), , , ,

でも上記のように tbl_tag_relations に同じ値を入れよりは、次のような tbl_count_relations テーブルを別途作成した方がいいでしょうか？

create table tbl_count_relations (
relation_id int,
count_relations int default 1,
CONSTRAINT fk_01 FOREIGN KEY ( relation_id ) REFERENCES tbl_tag_relations ( relation_id )
);

しかし別途テーブルを作成するとSELECTの際のJOINのコストがかかりますから、やはり同じ値にはなっても count_relations カラムは tbl_tag_relations テーブルに設けたほうがよろしいでしょうか？

Answer 2

重視するのが検索性能＞リソース量で、それぞれに検索のメリットがあるなら、2つとも作成しておき使い分けるというのもアリだと思います。

Comments

[nikuatsu]

なるほど。リソース量、そうですよね。ありがとうございます。