質問: ### 実現したいこと まずは画面UI上で実現したいことを説明します。 一覧画面で一覧検索と合わせて、サイドバーに件数を表示したいです。 例えばサイドバーには国と承認済み・未承認タグ、およびそれぞれの件数が表示されているとします。 (□はチェックボックス) ーーーーー □日本 10件 □米国 4件 □中国 4件 □承認済み 14件 □未承認 4件 ーーーーー 日本をチェックすると以下の通り、 承認済み、未承認は日本に絞られた件数で再検索されます。(一覧も再検索されます。) ーーーーー ■日本 10件 ←ここをチェックすると □米国 4件 □中国 4件 □承認済み 7件 ←件数が変わる(日本10件のうち承認済みが7件と言う意味) □未承認 3件 ←件数が変わる(日本10件のうち未承認が3件と言う意味) ーーーーー ### 準備したもの 取得元のテーブルは以下の通りです。 ・一覧テーブル | id | 国 | 承認フラグ | その他一覧情報 | | --- | --- | --- | --- | | 1 | 日本 | 承認済み | 〜 | | 2 | 米国 | 未承認 | 〜 | ### 現状の実現方法 上記を実現するために以下のSQL実行しています。 ``` -- 一覧を表示するためのSQL select その他一覧情報 from 一覧テーブル where 国 in (チェックされた国) and 承認フラグ in (チェックされた承認フラグ) and (その他一覧情報の絞り込み) ``` ``` -- 国の件数を表示するためのSQL select 国,count(*) from 一覧テーブル where 承認フラグ in (チェックされた承認フラグ) and (その他一覧情報の絞り込み) group by 国 ``` ``` -- 承認フラグの件数を表示するためのSQL select 承認フラグ,count(*) from 一覧テーブル where 国 in (チェックされた国) and (その他一覧情報の絞り込み) group by 承認フラグ ``` ### 現状の実現方法の課題 実際には上記以上にサイドバーのタグが複雑であること、データ量が多いことが原因で、 性能的にかなり問題が出ています。 ### 試したこと * 1つのSQLにまとめる →思いつきませんでした。 * 件数テーブルを別で準備 →その他一覧情報の条件ごとに持つ必要があり、その中には全文検索も含まれるので不可 * マルチスレッドなど並列処理 →PHPのため、困難と判断 ### 質問 上記実現したいことを性能問題を回避して実現するためにはどのようなアプローチがありますか。 過去の事例や世の中にあるプロダクトでどのように扱っているかアドバイスいただけないでしょうか。 方針レベルでも思いつく点があれば教えていただけますと幸いです。 最後に自分で思いつくところを列挙します。 * ElasticsearchやRedisの利用 →実現できるか不明 * Ajaxで件数を非同期取得 →根本解決にはならない気がしている ### 環境 * MySQL5.7 * PHP7.2 ※上記、現行環境ですが、あまりこだわりません。 よろしくお願いします。
回答: > > ■日本 10件 ←ここをチェックすると > > □米国 5件 > > □中国 5件 > > > □承認済み 7件 ←件数が変わる > > □未承認 3件 ←件数が変わる > > > 上記、もし未チェック分を表示する処理ならチェックしたもの以外を検索するので ``` select カテゴリ,count(*) from tbl where not 国 in('日本') ``` のような処理になります sample ====== * 送り側 ※今回は面倒なのでjQueryで ``` 日本:10件 米国:4件 中国:4件 承認済み:0件 未承認:0件 ``` * 受け側(recv.php) ``` 0){ $sql.="and 国 in (".implode(",",array_fill(0,count($country),"?")).")"; $data=$country; } $sql.=" group by カテゴリ"; print $sql.";\n"; print_r($data); /* 実際には$sqlをprepareで処理してカテゴリごとの件数をjsonで返すのがベター */ ``` DB設計について ======== 集計のロジックを変えます テーブルはこんな感じになります ``` create table tbl(id int primary key,国 varchar(10),承認 varchar(10),その他 varchar(10)); insert into tbl values ( 1,'日本','承認済み','その他1'), ( 2,'日本','承認済み','その他1'), ( 3,'日本','承認済み','その他2'), ( 4,'日本','承認済み','その他2'), ( 5,'日本','承認済み','その他2'), ( 6,'日本','承認済み','その他2'), ( 7,'日本','承認済み','その他3'), ( 8,'日本','未承認','その他1'), ( 9,'日本','未承認','その他2'), (10,'日本','未承認','その他2'), (11,'米国','承認済み','その他1'), (12,'米国','承認済み','その他1'), (13,'米国','承認済み','その他1'), (14,'米国','未承認','その他3'), (15,'中国','承認済み','その他1'), (16,'中国','承認済み','その他2'), (17,'中国','承認済み','その他3'), (18,'中国','承認済み','その他2'); ``` * 絞り込みなし ``` select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl group by 国 union all select '承認' as カテゴリ,承認 as 要素,sum(1) as 件数 from tbl group by 承認 union all select 'その他' as カテゴリ,その他 as 要素,sum(1) as 件数 from tbl group by その他 ``` * 国が'日本' 国の集計以外の件数集計にor条件を追加します ``` select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl group by 国 union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認 union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by その他 ``` * 国が'日本'または承認が'未承認' ``` select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国 union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認 union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本') or 承認 in('未承認'))) as 件数 from tbl group by その他 ``` これを更に集計するとこう ``` select カテゴリ,group_concat(concat(要素,":",件数)) as 要素件数 from( select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国 union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認 union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本') or 承認 in('未承認'))) as 件数 from tbl group by その他 ) as sub group by カテゴリ ``` * 結果: | カテゴリ | 要素件数 | | --- | --- | | その他 | その他2:6,その他3:2,その他1:3 | | 国 | 中国:0,日本:3,米国:1 | | 承認 | 承認済み:7,未承認:3 | これを受けてjsonを返してやればよいでしょう ※注意 where句で絞り込みをすると0件のデータが表示されなくなります。 0と表示したいなら今回のようなsumで集計、 表示しなくてもいいなら(つまりjs側で例外処理が必要)where句で絞り込みをしてください