結局その３テーブルからどういう結果を得たいのですか？
実現したいこと
以下のCREATE、INSERTから、次の結果を得たいです。
これは '指導' のタグを持つコメントを取得した結果です。
SQL
+----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+ | ID | author_tag_names | author_is_officials | author_details | genre_tag_names | genre_is_officials | genre_details | +----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+ | 1 | 鈴木__SEPARATOR__佐藤 | 1__SEPARATOR__0 | 良い人__SEPARATOR__悪い人 | 指導 | 1 | . | +----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+ | 3 | NULL | NULL | NULL | 指導 | 1 | . | +----+----------------------+---------------------+--------------------------+-----------------+--------------------+---------------+
CREATE、INSERT
SQL
-- コメント CREATE TABLE my_comments( `ID` int, `lang_id` int, `comment` varchar(10), count_likes int(10), PRIMARY KEY (`ID`), FULLTEXT full_my_comments (`comment`) WITH PARSER ngram ); INSERT INTO my_comments (`ID`, `lang_id`, `comment`, `count_likes`) VALUES (1, 1, '眠らないでください', 5), (2, 1, '静かにしてください', 1), (3, 1, '席についてください', 6), (4, 1, 'たまに休んで下さい', 9); -- タグ CREATE TABLE my_tags( `ID` int, `is_official` int, `tag_kind_id` int, `tag_name` varchar(100), `detail` varchar(100), PRIMARY KEY (`ID`), UNIQUE unique_tags (`is_official`, `tag_kind_id`, `tag_name`, `detail`) ); INSERT INTO my_tags (`ID`, `is_official`, `tag_kind_id`, `tag_name`, `detail`) VALUES (1, 1, 1, '鈴木', '良い人'), (2, 0, 1, '佐藤', '悪い人'), (3, 1, 2, '指導', '.'); -- コメントが持っているタグ CREATE TABLE my_tag_holders( `comments_ID` int, `tags_ID` int, PRIMARY KEY (`comments_ID`,`tags_ID`) ); INSERT INTO my_tag_holders (`comments_ID`, `tags_ID`) VALUES (1, 1),(1, 2),(1, 3), (2, 4), (3, 3);
実現するための自分なりのSELECT
自分でこれを実現する場合は次のSELECTしか書けず、GROUP_CONCAT が多すぎてしまいます。
ご覧のように「GROUP_CONCAT の回数」は、「 tag_kind_id の値の数 ✕ カラムの数 」という２つで構想されているので、次のSELECTでは６回になっています…
…が、実際にはこの２つはもっと多いので GROUP_CONCAT だらけ（５０回くらい）になってしまい、速度が遅くなるのです。
SQL
-- '指導' で検索した結果を取得 SELECT comments.ID # tag_kind_id=1 の tag_name ,GROUP_CONCAT( CASE WHEN tags.tag_kind_id=1 THEN tags.tag_name ELSE null END SEPARATOR '__SEPARATOR__' ) AS author_tag_names # tag_kind_id=1 の is_official ,GROUP_CONCAT( CASE WHEN tags.tag_kind_id=1 THEN tags.is_official ELSE null END SEPARATOR '__SEPARATOR__' ) AS author_is_officials # tag_kind_id=1 の details ,GROUP_CONCAT( CASE WHEN tags.tag_kind_id=1 THEN tags.detail ELSE null END SEPARATOR '__SEPARATOR__' ) AS author_details # tag_kind_id=2 の tag_name ,GROUP_CONCAT( CASE WHEN tags.tag_kind_id=2 THEN tags.tag_name ELSE null END SEPARATOR '__SEPARATOR__' ) AS genre_tag_names # tag_kind_id=2 の is_official ,GROUP_CONCAT( CASE WHEN tags.tag_kind_id=2 THEN tags.is_official ELSE null END SEPARATOR '__SEPARATOR__' ) AS genre_is_officials # tag_kind_id=2 の details ,GROUP_CONCAT( CASE WHEN tags.tag_kind_id=2 THEN tags.detail ELSE null END SEPARATOR '__SEPARATOR__' ) AS genre_details FROM my_comments comments LEFT JOIN my_tag_holders th ON th.comments_ID = comments.ID LEFT JOIN my_tags tags ON tags.ID = th.tags_ID -- タグ名を指定するためのJOIN LEFT JOIN my_tag_holders th2 ON th2.comments_ID = comments.ID LEFT JOIN my_tags tags2 ON tags2.ID = th2.tags_ID -- タグ名を指定 WHERE tags2.tag_name = '指導' GROUP BY comments.ID LIMIT 0, 5
補足
ちなみに、SELECTの結果には、PHPで次のset_tag_datas_to_row()をかけて、この$resultが最終的にフロントへ出力する値として求めるものになります。
PHP
<?php // SELECTの結果 $rows = [ ['ID'=>1, 'author_tag_names'=>'鈴木__SEPARATOR__佐藤','author_is_officials'=>'1__SEPARATOR__0','author_details'=>'良い人__SEPARATOR__悪い人', 'genre_tag_names'=>'指導','genre_is_officials'=>'1','genre_details'=>'.'], ['ID'=>2, 'author_tag_names'=>NULL,'author_is_officials'=>NULL,'author_details'=>NULL, 'genre_tag_names'=>'指導','genre_is_officials'=>'1','genre_details'=>'.'], ]; // 最終的にフロントへ出力する値 $result = array_map( 'set_tag_datas_to_row', $rows ); /* array ( 'ID' => 1, 'tag_datas' => array ( 0 => array ( 'tag_kind_name' => 'author', 'tag_name' => '鈴木', 'detail' => '良い人', 'is_official' => true, ), 1 => array ( 'tag_kind_name' => 'author', 'tag_name' => '佐藤', 'detail' => '悪い人', 'is_official' => false, ), 2 => array ( 'tag_kind_name' => 'genre', 'tag_name' => '指導', 'detail' => '.', 'is_official' => true, ), ), ), 1 => array ( 'ID' => 2, 'tag_datas' => array ( 0 => array ( 'tag_kind_name' => 'genre', 'tag_name' => '指導', 'detail' => '.', 'is_official' => true, ), ), ), ) */ // SELECTの結果に、tag_datas をセット function set_tag_datas_to_row( $row ){ $result = []; $info = []; foreach( $row as $key => $data ){ if( is_null($data) ) continue; // タグの情報なら分解して $tag_kind_name ごとの配列を作る if( in_array( mb_strstr( $key, '_', true), ['author','genre'], true ) ){ // 分解 preg_match( '/([^_]*)_(.+)/', $key, $matched ); // ex. $key = 'author_tag_names'; $tag_kind_name = $matched[1]; // ex. $tag_kind_name = 'author' $col = rtrim( $matched[2], 's' ); // ex. $col = 'tag_name' // 配列を作る foreach( explode( '__SEPARATOR__', $data ) as $i => $v ) $info[$tag_kind_name][$i][$key] = explode( '__SEPARATOR__', $row[$tag_kind_name.'_'.$col.'s'] )[$i]; // それ以外ならそのままセット }else{ $result[$key] = $data; } } // カラムごとにセット foreach( array_keys($info) as $tag_kind_name ){ foreach( $info[$tag_kind_name] as $arr ){ $result['tag_datas'][] = [ 'tag_kind_name' => $tag_kind_name, 'tag_name' => $arr[$tag_kind_name.'_tag_names'], 'detail' => $arr[$tag_kind_name.'_details'], 'is_official' => $arr[$tag_kind_name.'_is_officials'] === '1', ]; } } return $result; }
CREATE、INSERT、$result 以外は変えてしまって構いません。
この多すぎるGROUP_CONCATをなんとか省略する方法を知りたいです。
宜しくお願いいたします。
バージョン
PHP 7.2
MySQL 5.7
長くお見苦しいコードですみません。
テーブルから得たい結果は$rowsで、これは現状のSELECTで得られます。
しかし最終的に得たい結果は$resultですので、$resultを得られるならば$rowsは変えてしまって構いません。
質問は修正できます。
何をやりたいのか掲示板を読んでいる人が理解できるように、差し支えない範囲で実際のデータをINSERT文で、SELECT結果と共に Markdownの[コード]で提示しては？
https://teratail.com/help/question-tips#questionTips37
ありがとうございます。修正させて頂きました。（まだ分かりにくかったら申し訳ございません。）
速度という話なら、先ずは実行計画の確認ですね。
saziさん、コメント誠にありがとうございます。
こちらの質問は実行計画で解決に至りますでしょうか…？
実際に質問のような多すぎるGROUP_CONTCATを次の１つにまとめてみましたが、
,GROUP_CONCAT(
tags.ID
SEPARATOR ' '
) AS tag_ids
しかし、こう１つにまとめた場合と、質問のように多すぎる場合とを比べても、実行計画の結果は全てにおいて同一でした。
このことから、単にGROUP_CONTCATが多すぎるせいで遅いのだろうということが分かるので、GROUP_CONTCATをいかに減らすかに焦点を当てるべき問題だと、個人的には思えるのですが…
尚、質問のようにGROUP_CONTCATが多すぎる場合は0.9秒で、上記のように１つにまとめた場合は0.3秒でした。my_commentsは５万件、my_tagsは５万件、my_tag_holdersは30万件です。
> こちらの質問は実行計画で解決に至りますでしょうか…？
解決に至るかどうかは分かりませんが、解決に至る為の情報としてあった方が良いと思います。
なるほど。GROUP_CONTCATを省略できるような書き方についてはやはり難しそうですね。ありがとうございます。
GROUP_CONTCAT をなくし、さらにそもそも GROUP BY をなくすと0.5秒になったので、この後にPHPで GROUP_CONTCAT 的なことをするのがマシな解決策かもしれません。
上記ダメでした。 GROUP BY をなくすとコメントの取得件数が指定できませんでした。難しいです…。
まだ回答がついていません
会員登録して回答してみよう
