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

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

ただいまの
回答率

90.33%

  • PHP

    21413questions

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

  • MySQL

    6207questions

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

  • SQL

    2577questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Elasticsearch

    115questions

    Elasticsearchは、クラウド向けに構築された、RESTful な API を提供する分散型のサーチエンジンアプリケーションです。

  • Redis

    111questions

    Redisは、オープンソースのkey-valueデータストアで、NoSQLに分類されます。すべてのデータをメモリ上に保存するため、処理が極めて高速です。

一覧検索と分類ごとの件数取得を効率的に行いたい

受付中

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 77

rytskywlkr

score 3

実現したいこと

まずは画面UI上で実現したいことを説明します。

一覧画面で一覧検索と合わせて、サイドバーに件数を表示したいです。

例えばサイドバーには国と承認済み・未承認タグ、およびそれぞれの件数が表示されているとします。
(□はチェックボックス)
ーーーーー
□日本 10件
□米国 4件
□中国 4件

□承認済み 14件
□未承認 4件
ーーーーー

日本をチェックすると以下の通り、
承認済み、未承認は日本に絞られた件数で再検索されます。(一覧も再検索されます。)
ーーーーー
■日本 10件 ←ここをチェックすると
□米国 4件
□中国 4件

□承認済み 7件 ←件数が変わる(日本10件のうち承認済みが7件と言う意味)
□未承認 3件  ←件数が変わる(日本10件のうち未承認が3件と言う意味)
ーーーーー

準備したもの

取得元のテーブルは以下の通りです。
・一覧テーブル

id 承認フラグ その他一覧情報
日本 承認済み
米国 未承認

現状の実現方法

上記を実現するために以下のSQL実行しています。

-- 一覧を表示するためのSQL
select
  その他一覧情報
from
  一覧テーブル
wherein (チェックされた国) and
  承認フラグ in (チェックされた承認フラグ) and
  (その他一覧情報の絞り込み)
-- 国の件数を表示するためのSQL
select
  国,count(*)
from
  一覧テーブル
where
  承認フラグ in (チェックされた承認フラグ) and
  (その他一覧情報の絞り込み)
group by
-- 承認フラグの件数を表示するためのSQL
select
  承認フラグ,count(*)
from
  一覧テーブル
wherein (チェックされた国) and
  (その他一覧情報の絞り込み)
group by
  承認フラグ

現状の実現方法の課題

実際には上記以上にサイドバーのタグが複雑であること、データ量が多いことが原因で、
性能的にかなり問題が出ています。

試したこと

  • 1つのSQLにまとめる
    →思いつきませんでした。
  • 件数テーブルを別で準備
    →その他一覧情報の条件ごとに持つ必要があり、その中には全文検索も含まれるので不可
  • マルチスレッドなど並列処理
    →PHPのため、困難と判断

質問

上記実現したいことを性能問題を回避して実現するためにはどのようなアプローチがありますか。
過去の事例や世の中にあるプロダクトでどのように扱っているかアドバイスいただけないでしょうか。
方針レベルでも思いつく点があれば教えていただけますと幸いです。

最後に自分で思いつくところを列挙します。

  • ElasticsearchやRedisの利用
    →実現できるか不明
  • Ajaxで件数を非同期取得
    →根本解決にはならない気がしている

環境

  • MySQL5.7
  • PHP7.2
    ※上記、現行環境ですが、あまりこだわりません。

よろしくお願いします。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

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

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

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

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

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

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

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

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

+1

■日本 10件 ←ここをチェックすると
□米国 5件
□中国 5件

□承認済み 7件 ←件数が変わる
□未承認 3件  ←件数が変わる

上記、もし未チェック分を表示する処理ならチェックしたもの以外を検索するので

select カテゴリ,count(*) from tbl where notin('日本')


のような処理になります

sample

  • 送り側
    ※今回は面倒なのでjQueryで
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script>
$(function(){
  $('[name="country[]"]').on('change',function(){
    $.ajax({
    url:"recv.php",
    data:$('[name="country[]"]:checked').map(function(){
      return "country[]="+encodeURIComponent($(this).val());
    }).get().join("&"),
    }).done(function(data){
      console.log(data);
      /* 実際にはここで承認済み、非承認の件数を書き換える */
    });
  });
});
</script>

<div><label><input type="checkbox" name="country[]" value="日本">日本</label>:10件</div>
<div><label><input type="checkbox" name="country[]" value="米国">米国</label>:4件</div>
<div><label><input type="checkbox" name="country[]" value="中国">中国</label>:4件</div>

<ul>
<li>承認済み:<span id="shouninzumi">0</span><li>未承認:<span id="mishounin">0</span></ul>
  • 受け側(recv.php)
<?PHP
$country=filter_input(INPUT_GET,"country", FILTER_DEFAULT , FILTER_REQUIRE_ARRAY);
$sql ="select カテゴリ,count(*) as 件数from tbl where 1 ";
$data=[];
if(count((array) $country)>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 byunion 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 byunion all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 orin('日本'))) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 orin('日本'))) as 件数 from tbl group by その他
  • 国が'日本'または承認が'未承認'
select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group byunion all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 orin('日本'))) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 orin('日本') 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 byunion all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 orin('日本'))) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 orin('日本') 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句で絞り込みをしてください

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/06 11:33

    ご回答ありがとうございます。
    質問が悪かったので、すぐに直しますが、
    欲しいのは、チェックされたものの件数です。
    「日本」のうち「承認済み」が7件、「未承認」が3件と言う意味です。

    というのと、こういったSQLをカテゴリごとに都度発行するのが性能劣化につながっている認識です。

    キャンセル

  • 2018/12/06 12:05

    それはロジックがおかしい。
    チェック済みを表示したいなら最初はすべてにチェックをいれるべき

    キャンセル

  • 2018/12/06 12:15

    はい、厳密にはその通りです。
    ただUI上,ユーザが絞り込みやすいように、初期表示はチェックを全て外しています。
    チェックが全て外れている場合は全チェックと同じ扱いとするようにロジックを組んでいます。
    今回の質問の意図と異なるところで混乱させてしまいすみません。

    キャンセル

  • 2018/12/06 12:41

    一応指定された仕様どおりの流れを書いておきました
    ユーザーが混乱しないように十分配慮してください

    キャンセル

  • 2018/12/06 13:45

    詳細な記載ありがとうございます。
    今まさに記載いただいた処理の流れで作っております。
    (なお承認済み,未承認についてもチェックボックスのイメージです。)

    一方でこの方法だと現状で3本のSQLが必要(本文記載の通り)ですし、
    実際には「地域」「承認フラグ」以外のカテゴリもあるため、SQLが増えてしまいます。

    このロジックではない方法で実現できないか教えていただけると助かります。
    それが上記「質問」の意図でした。

    キャンセル

  • 2018/12/06 13:51

    > 現状で3本のSQLが必要

    多分考え方がおかしいか、仕様を正しく提示していないかだと思います
    集計単位がちがうのですから、集計単位ごとにSQLを発行するしかありません

    キャンセル

  • 2018/12/06 14:48

    DBの設計から見直しました、考え方を精査してみて下さい

    キャンセル

  • 2018/12/06 15:47

    詳細にありがとうございます。大変参考になります。

    いただいたSQLで実現可能なのですが、unionするのと複数回sqlを発行するのは、あまり性能差がないような気がします。

    より具体的な性能の懸念点を説明させて下さい。
    上記tblにcontentsというtext型のカラムがあると仮定します。

    それをlike検索しようとすると

    ```
    select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl where contents like '%あいうえお%' group by 国
    union all select '承認' as カテゴリ,承認 as 要素,sum(1) as 件数 from tbl where contents like '%あいうえお%' group by 承認
    union all select 'その他' as カテゴリ,その他 as 要素,sum(1) as 件数 from tbl where contents like '%あいうえお%' group by その他
    ```

    となると思います。

    そうすると一番コストがかかるであろうlike検索を同じ内容で3回走らせないといけません。
    ですので、できるだけ1回で発行したいと考えております。

    キャンセル

  • 2018/12/06 15:52

    なんどもいうようで申し訳ないですが、ロジックがおかしいので実現は基本的に不可能ですし
    そもそもlikeで前方後方一致を書いている時点でスピード云々はあきらめてください
    unionしているのはスピードのためではなく集計を一括するためですので
    当然高速化はできません

    キャンセル

  • 2018/12/06 16:00 編集

    ロジックについては、初期表示時に全部チェックされている仕様と考えていただいて差し支えありません。
    likeについては、便宜的にそのようにお伝えしましたが実際は性能に配慮してngramによる全文検索です。
    (説明が不足しており申し訳ございません。)
    SQLでの速度改善が難しいとわかったのでその点はよかったです。
    ありがとうございます。

    別のアプローチがあれば良いのですが、、

    キャンセル

  • 2018/12/06 16:09

    たとえば、国=日本としたときに、米国、中国を0にしていいならやりようはありますよ
    でもそうじゃないのですよね?
    そうなると集計単位も検索条件もまとめようがないのできびしいです。

    キャンセル

  • 2018/12/06 17:05

    国を日本にしたタイミングでは米国,中国を0とするのは良いです。
    その場合は米国,中国の件数を変えないようにjqueryなどで制御すれば良いので。
    ただ国に日本が選ばれている状態で、承認のチェックが変更された場合は、
    承認の状態に合わせて米国,中国の件数も変更したいです。

    キャンセル

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

  • ただいまの回答率 90.33%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

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

  • PHP

    21413questions

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

  • MySQL

    6207questions

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

  • SQL

    2577questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Elasticsearch

    115questions

    Elasticsearchは、クラウド向けに構築された、RESTful な API を提供する分散型のサーチエンジンアプリケーションです。

  • Redis

    111questions

    Redisは、オープンソースのkey-valueデータストアで、NoSQLに分類されます。すべてのデータをメモリ上に保存するため、処理が極めて高速です。