GROUP_CONCAT を省略したい

実現したいこと

以下のCREATE、INSERTから、次の結果を得たいです。
これは '指導' のタグを持つコメントを取得した結果です。

SQL
1+----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+
2| ID |   author_tag_names   | author_is_officials |      author_details      | genre_tag_names | genre_is_officials | genre_details |
3+----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+
4| 1  | 鈴木__SEPARATOR__佐藤 |   1__SEPARATOR__0   | 良い人__SEPARATOR__悪い人 |       指導       |        1          |       .       |
5+----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+
6| 3  |        NULL          |        NULL         |           NULL           |       指導       |        1          |       .       |
7+----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+

CREATE、INSERT

SQL
1-- コメント
2CREATE TABLE my_comments(
3    `ID` int, `lang_id` int, `comment` varchar(10), count_likes int(10),
4    PRIMARY KEY (`ID`),
5    FULLTEXT full_my_comments (`comment`) WITH PARSER ngram );
6INSERT INTO my_comments
7    (`ID`, `lang_id`, `comment`, `count_likes`)
8    VALUES
9    (1,  1,  '眠らないでください',  5),
10    (2,  1,  '静かにしてください',  1),
11    (3,  1,  '席についてください',  6),
12    (4,  1,  'たまに休んで下さい',  9);
13
14-- タグ
15CREATE TABLE my_tags(
16    `ID` int, `is_official` int, `tag_kind_id` int, `tag_name` varchar(100), `detail` varchar(100),
17    PRIMARY KEY (`ID`),
18    UNIQUE unique_tags (`is_official`, `tag_kind_id`, `tag_name`, `detail`) );
19INSERT INTO my_tags
20    (`ID`, `is_official`, `tag_kind_id`, `tag_name`, `detail`)
21    VALUES
22    (1,  1,  1,  '鈴木', '良い人'),
23    (2,  0,  1,  '佐藤', '悪い人'),
24    (3,  1,  2,  '指導', '.');
25
26-- コメントが持っているタグ
27CREATE TABLE my_tag_holders(
28    `comments_ID` int, `tags_ID` int,
29    PRIMARY KEY (`comments_ID`,`tags_ID`) );
30INSERT INTO my_tag_holders
31    (`comments_ID`, `tags_ID`)
32    VALUES
33    (1, 1),(1, 2),(1, 3),
34    (2, 4),
35    (3, 3);

実現するための自分なりのSELECT

自分でこれを実現する場合は次のSELECTしか書けず、GROUP_CONCAT が多すぎてしまいます。

ご覧のように「GROUP_CONCAT の回数」は、「 tag_kind_id の値の数 ✕ カラムの数」という２つで構想されているので、次のSELECTでは６回になっています…

…が、実際にはこの２つはもっと多いので GROUP_CONCAT だらけ（５０回くらい）になってしまい、速度が遅くなるのです。

SQL
1-- '指導' で検索した結果を取得
2SELECT
3    comments.ID
4        
5    # tag_kind_id=1 の tag_name
6    ,GROUP_CONCAT(
7        CASE WHEN tags.tag_kind_id=1
8        THEN tags.tag_name
9        ELSE null END
10        SEPARATOR '__SEPARATOR__'
11    ) AS author_tag_names
12 
13     # tag_kind_id=1 の is_official
14    ,GROUP_CONCAT(
15        CASE WHEN tags.tag_kind_id=1
16        THEN tags.is_official
17        ELSE null END
18        SEPARATOR '__SEPARATOR__'
19    ) AS author_is_officials
20
21     # tag_kind_id=1 の details
22    ,GROUP_CONCAT(
23        CASE WHEN tags.tag_kind_id=1
24        THEN tags.detail
25        ELSE null END
26        SEPARATOR '__SEPARATOR__'
27    ) AS author_details
28        
29    # tag_kind_id=2 の tag_name
30    ,GROUP_CONCAT(
31        CASE WHEN tags.tag_kind_id=2
32        THEN tags.tag_name
33        ELSE null END
34        SEPARATOR '__SEPARATOR__'
35    ) AS genre_tag_names    
36    
37     # tag_kind_id=2 の is_official
38    ,GROUP_CONCAT(
39        CASE WHEN tags.tag_kind_id=2
40        THEN tags.is_official
41        ELSE null END
42        SEPARATOR '__SEPARATOR__'
43    ) AS genre_is_officials
44
45     # tag_kind_id=2 の details
46    ,GROUP_CONCAT(
47        CASE WHEN tags.tag_kind_id=2
48        THEN tags.detail
49        ELSE null END
50        SEPARATOR '__SEPARATOR__'
51    ) AS genre_details
52
53FROM
54    my_comments comments
55    LEFT JOIN my_tag_holders th
56            ON th.comments_ID = comments.ID
57    LEFT JOIN my_tags tags
58            ON tags.ID = th.tags_ID
59
60    -- タグ名を指定するためのJOIN
61    LEFT JOIN my_tag_holders th2
62            ON th2.comments_ID = comments.ID
63    LEFT JOIN my_tags tags2
64            ON tags2.ID = th2.tags_ID
65                  
66-- タグ名を指定          
67WHERE
68    tags2.tag_name = '指導'
69    GROUP BY comments.ID    
70    LIMIT 0, 5    
71

補足

ちなみに、SELECTの結果には、PHPで次のset_tag_datas_to_row()をかけて、この$resultが最終的にフロントへ出力する値として求めるものになります。

PHP
1<?php
2// SELECTの結果
3$rows = [
4    ['ID'=>1,
5    'author_tag_names'=>'鈴木__SEPARATOR__佐藤','author_is_officials'=>'1__SEPARATOR__0','author_details'=>'良い人__SEPARATOR__悪い人',
6    'genre_tag_names'=>'指導','genre_is_officials'=>'1','genre_details'=>'.'],
7    ['ID'=>2,
8    'author_tag_names'=>NULL,'author_is_officials'=>NULL,'author_details'=>NULL,
9    'genre_tag_names'=>'指導','genre_is_officials'=>'1','genre_details'=>'.'], 
10];
11
12// 最終的にフロントへ出力する値
13$result = array_map( 'set_tag_datas_to_row', $rows );
14/*
15  array (
16    'ID' => 1,
17    'tag_datas' => 
18    array (
19      0 => 
20      array (
21        'tag_kind_name' => 'author',
22        'tag_name' => '鈴木',
23        'detail' => '良い人',
24        'is_official' => true,
25      ),
26      1 => 
27      array (
28        'tag_kind_name' => 'author',
29        'tag_name' => '佐藤',
30        'detail' => '悪い人',
31        'is_official' => false,
32      ),
33      2 => 
34      array (
35        'tag_kind_name' => 'genre',
36        'tag_name' => '指導',
37        'detail' => '.',
38        'is_official' => true,
39      ),
40    ),
41  ),
42  1 => 
43  array (
44    'ID' => 2,
45    'tag_datas' => 
46    array (
47      0 => 
48      array (
49        'tag_kind_name' => 'genre',
50        'tag_name' => '指導',
51        'detail' => '.',
52        'is_official' => true,
53      ),
54    ),
55  ),
56)
57*/
58
59// SELECTの結果に、tag_datas をセット
60function set_tag_datas_to_row( $row ){
61	$result = [];
62    
63    $info = [];
64	foreach( $row as $key => $data ){
65		if( is_null($data) ) continue;
66		
67		// タグの情報なら分解して $tag_kind_name ごとの配列を作る
68		if( in_array( mb_strstr( $key, '_', true), ['author','genre'], true ) ){
69			
70			// 分解
71			preg_match( '/([^_]*)_(.+)/', $key, $matched ); // ex. $key = 'author_tag_names'; 
72			$tag_kind_name = $matched[1];                   // ex. $tag_kind_name = 'author'
73			$col           = rtrim( $matched[2], 's' );     // ex. $col = 'tag_name'
74			
75			// 配列を作る
76			foreach( explode( '__SEPARATOR__', $data ) as $i => $v )
77			$info[$tag_kind_name][$i][$key] = explode( '__SEPARATOR__', $row[$tag_kind_name.'_'.$col.'s'] )[$i];
78	
79		// それ以外ならそのままセット
80		}else{
81			$result[$key] = $data;
82		}
83	
84	}
85	
86	// カラムごとにセット
87	foreach( array_keys($info) as $tag_kind_name ){
88	    foreach( $info[$tag_kind_name] as $arr ){
89			$result['tag_datas'][] = [
90    		    'tag_kind_name' => $tag_kind_name,
91    		    'tag_name'      => $arr[$tag_kind_name.'_tag_names'],
92    		    'detail'        => $arr[$tag_kind_name.'_details'],
93				'is_official'   => $arr[$tag_kind_name.'_is_officials'] === '1',			
94			];
95	    }
96	}
97
98	return $result;
99}
100

CREATE、INSERT、$result 以外は変えてしまって構いません。
この多すぎるGROUP_CONCATをなんとか省略する方法を知りたいです。
宜しくお願いいたします。

バージョン

PHP 7.2
MySQL 5.7

yambejp

2022/04/22 10:33

結局その３テーブルからどういう結果を得たいのですか？

nikuatsu

2022/04/22 10:39

長くお見苦しいコードですみません。テーブルから得たい結果は$rowsで、これは現状のSELECTで得られます。しかし最終的に得たい結果は$resultですので、$resultを得られるならば$rowsは変えてしまって構いません。

Orlofsky

2022/04/22 20:07

質問は修正できます。何をやりたいのか掲示板を読んでいる人が理解できるように、差し支えない範囲で実際のデータをINSERT文で、SELECT結果と共に Markdownの[コード]で提示しては？ https://teratail.com/help/question-tips#questionTips37

nikuatsu

2022/04/23 06:38

ありがとうございます。修正させて頂きました。（まだ分かりにくかったら申し訳ございません。）

sazi

2022/04/29 13:47

速度という話なら、先ずは実行計画の確認ですね。

nikuatsu

2022/05/01 07:03 編集

saziさん、コメント誠にありがとうございます。こちらの質問は実行計画で解決に至りますでしょうか…？実際に質問のような多すぎるGROUP_CONTCATを次の１つにまとめてみましたが、 ,GROUP_CONCAT( tags.ID SEPARATOR ' ' ) AS tag_ids しかし、こう１つにまとめた場合と、質問のように多すぎる場合とを比べても、実行計画の結果は全てにおいて同一でした。このことから、単にGROUP_CONTCATが多すぎるせいで遅いのだろうということが分かるので、GROUP_CONTCATをいかに減らすかに焦点を当てるべき問題だと、個人的には思えるのですが…

nikuatsu

2022/05/01 07:11

尚、質問のようにGROUP_CONTCATが多すぎる場合は0.9秒で、上記のように１つにまとめた場合は0.3秒でした。my_commentsは５万件、my_tagsは５万件、my_tag_holdersは30万件です。

sazi

2022/05/01 12:22

> こちらの質問は実行計画で解決に至りますでしょうか…？解決に至るかどうかは分かりませんが、解決に至る為の情報としてあった方が良いと思います。

nikuatsu

2022/05/01 14:27

なるほど。GROUP_CONTCATを省略できるような書き方についてはやはり難しそうですね。ありがとうございます。

nikuatsu

2022/05/02 12:15

GROUP_CONTCAT をなくし、さらにそもそも GROUP BY をなくすと0.5秒になったので、この後にPHPで GROUP_CONTCAT 的なことをするのがマシな解決策かもしれません。