質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

解決済

GROUP_CONCAT を省略したい

nikuatsu
nikuatsu

総合スコア154

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

2回答

0評価

0クリップ

290閲覧

投稿2022/04/22 10:27

編集2022/06/12 03:20

実現したいこと

以下の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 の値の数 ✕ カラムの数 」という2つで構想されているので、次のSELECTでは6回になっています…

…が、実際にはこの2つはもっと多いので GROUP_CONCAT だらけ(50回くらい)になってしまい、速度が遅くなるのです。

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

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

yambejp

2022/04/22 10:33

結局その3テーブルからどういう結果を得たいのですか?
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を次の1つにまとめてみましたが、 ,GROUP_CONCAT( tags.ID SEPARATOR ' ' ) AS tag_ids しかし、こう1つにまとめた場合と、質問のように多すぎる場合とを比べても、実行計画の結果は全てにおいて同一でした。 このことから、単にGROUP_CONTCATが多すぎるせいで遅いのだろうということが分かるので、GROUP_CONTCATをいかに減らすかに焦点を当てるべき問題だと、個人的には思えるのですが…
nikuatsu

2022/05/01 07:11

尚、質問のようにGROUP_CONTCATが多すぎる場合は0.9秒で、上記のように1つにまとめた場合は0.3秒でした。my_commentsは5万件、my_tagsは5万件、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 的なことをするのがマシな解決策かもしれません。
nikuatsu

2022/05/02 12:30

上記ダメでした。 GROUP BY をなくすとコメントの取得件数が指定できませんでした。難しいです…。

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。