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

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

ただいまの
回答率

90.62%

  • SQL

    2330questions

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

サブクエリが遅いのですが

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 2,194

vc3000

score 180

よくあるブログのデータベースで、users(ユーザー)とposts(投稿)が1対多で結びついているとします。
ユーザーごとの投稿数をつけてユーザー一覧を出力したい場合、以下のようなSQLを書くとだいぶ遅くなってしまいます(同じサブクエリを2回書いているのも気になります)。

SELECT *
       ,(SELECT Count(*)
         FROM   posts
         WHERE  posts.user_id = users.id) cnt
FROM   users
ORDER  BY (SELECT Count(*)
           FROM   posts
           WHERE  posts.user_id = users.id) DESC

こういう場合、usersテーブルかどこかに投稿数のカラムを作っておくべきでしょうか?それともSQLの工夫でもっと速くする方法がありますか?
(MySQLを使っていますが、他のDBの場合も知りたいです)

追記:
ORDER BYは普通にcntと書けますね。
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 4

checkベストアンサー

+3

MySQLでしたら
コレで出して頂いた物よりはある程度速くなるんじゃないかなと思います。
SELECT
   U.*, 
   IFNULL(P.post_count, 0) AS cnt
FROM 
  users AS U 
LEFT JOIN 
  (SELECT
      COUNT(1) AS post_count,
      user_id
   FROM 
      posts 
   GROUP BY user_id ) AS P 
ON 
   U.id = P.user_id 
ORDER BY IFNULL(P.post_count, 0) DESC

また、INDEXの見直しや絞り込めるようならWHEREで絞り込んだ方が速くなると思います。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/08/01 17:15

    ありがとうございます。
    確かに実行時間が1/2~1/3になりました!

    キャンセル

+2

このSQLが重いのは、SELECTやORDERでのサブクエリが相関サブクエリになっていて、抽出行数と同じ回数のサブクエリが実行されているためではないかと思います。
(確認してませんが、サブクエリ内のusers.idが行毎に異なるので恐らく)
EXPLAINで調べてみると具体的にどうなっているか分かるかも知れません。参考ページ

ではどうすれば良いかというと、nanndemoiikara氏と殆ど同じです。
こちらのサブクエリ実行回数は1回で済むので、抽出行数が増えれば増えるほど実行コストは(相関サブクエリに比べて)小さくなると思います。
(その代わりusers各行とのマッチングコストは増えますが前者に比べて微々たるものです。またこのコストはusers.idにindexを作成することで軽減できます。)
SELECT U.*, IFNULL(P.post_count, 0) AS cnt
FROM users AS U 
LEFT JOIN (
    SELECT COUNT(*) AS post_count,user_id
    FROM posts 
    GROUP BY user_id 
  ) AS P ON U.id = P.user_id 
ORDER BY cnt DESC
order by の関数実行省いたのと、count(*)に変えただけです。
countはpostgresだと(*)の方が早かった(index使うから?)と思うのですが、mysqlだと(1)のほうが早いのかも知れません。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/08/01 19:37

    postgresだとアスタリスクの方が速くなるんですね。
    勉強になります。

    キャンセル

  • 2015/08/01 19:48 編集

    indexのついた項目だと、範囲指定や一致条件なら何番目~何番目のように抽出できるので行数を計算で出せるから抽出行が増えるほど...みたいな理屈だったはずですが、昔見たものなので今はどうなんでしょう?実際の所、どちらでも返答時間には殆ど影響無いです。

    キャンセル

+1

ちなみに、users, postsはそれぞれ何件で、遅いというのは具体的に何秒なのでしょうか?

SQLは単にこれでいいのではないかと思います。
SELECT * 
       , (SELECT Count(*)
         FROM   posts
         WHERE  posts.user_id = users.id) cnt
FROM   users
ORDER  BY cnt DESC

そこはさておき、このような検索だったら、postsのuser_idにインデックスをつけるべきです。絶対に。
それはやっていますか?やっていないから遅い気がするのですが…。違うのかな。

あと、usersの件数が多いようなら、*など使わずに実際に使用するカラムだけを指定すべきです。それだけで読み込みバイト数が大幅に削減でき、速度改善につながります。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/08/01 18:38

    usersだけではなく、postsのcountもINDEXの値の方が、速くなる 可 能 性 がありますね。

    キャンセル

  • 2015/08/01 19:12

    >usersだけではなく、postsのcountもINDEXの値の方が、速くなる 可 能 性 がありますね。

    usersテーブルへのアクセスは全く変わらないでしょう。一方でpostsテーブルへのアクセスは全く行わなくてよくなります。たぶん…。

    レコード件数がわかればいいので、インデックスアクセスを行いインデックスにあった件数を数えるだけで済むからです。論理的にはそうですよね。

    そして私の知る限りでは、OracleやDB2ではそのようになっています。この程度の工夫はMySQLでも実装されていてもいいと思いますが、もしかしたらMySQLではダメかもしれません。

    キャンセル

  • 2015/08/01 21:34

    手元ではusers、postsともに10万行のデータで、私の元のSQLがMySQLで1.26秒、Postgresで0.8秒かかっています。
    今実際にこのSQLが問題になっているのではなくて、一般的にこの形状の(各行ごとの集計が必要になる)SQLは遅くなりがちなので、画面表示で使う場合はどうするのが普通なのだろうと疑問に思っていました。背景を説明不足ですみませんでした。

    教えていただいたSQLでもレスポンスタイムの要件を満たせない場合は、あらかじめ件数を集計して持っておくしかないですよね(非正規化というのでしょうか?)

    キャンセル

  • 2015/08/01 23:48

    10万行ですか。。。
    > 教えていただいたSQLでもレスポンスタイムの要件を満たせない場合は、あらかじめ件数を集計して持っておくしかないですよね
    他の方もおっしゃっている用にSQL文だけでなくINDEX等の構造部分を見直してみてはいかがでしょうか?
    ただ、集計して持っておく速度には劣ってしまうとおもいますが。。。

    キャンセル

  • 2015/08/02 01:24

    10万行で1秒ですか…。1秒は遅すぎに感じます。

    DBサーバに使用しているパソコンもしくはサーバの性能にもよるとは思いますけど、3年以内発売の10万円程度のデスクトップパソコン程度の性能で、かつその程度の処理内容であれば、0.1秒以内に処理出来ると思いますけど…。


    >教えていただいたSQLでもレスポンスタイムの要件を満たせない場合は、
    >あらかじめ件数を集計して持っておくしかないですよね

    そうですね。最終手段として十分にありです。また、リアルタイムに集計せずとも、1日1~3回とかバッチで集計しても十分だと思います。

    ただ、この件に限らないことですが「しかない」という思い込み・決め付け思考はやめたほうがいいと思います。対応方法の可能性を狭めてしまうので。

    ただその前にインデックスです。postsのuser_idにインデックスは付いているのでしょうか?もしもインデックスがないのならSQLを変更する前にインデックスをつけましょう。それだけですべてが解決するかもしれません。

    キャンセル

  • 2015/08/04 07:35

    ありがとうございます。インデックスは付いています。
    環境はさくらVPS 512MB、1コアなので、スペックは低いと思います。

    キャンセル

+1

基本通りに書くのが一番速そうですけど、どうでしょうね。

SELECT 
    users.*
    , count(DISTINCT post_id) AS cnt
FROM 
    users
LEFT JOIN 
    posts
ON 
    users.user_id = posts.user_id
GROUP BY 
    users.user_id;

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `posts` (
  `post_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`post_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

手元の環境で試してみたけど、レコード数が少ないので、他の方の回答でも大差ありませんでしたが、Explain してみてやはり基本通りの方がインでクスが適切に使われる様子ですね。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/08/01 19:31 編集

    usersテーブルなのにuser_idは構造としてどうなのでしょうか?
    postsのuser_idにunsignedが付いていないのはなぜでしょうか?

    キャンセル

  • 2015/08/01 20:01 編集

    手元で試すだけだからそうしただけです。
    なんせ、質問者自身が構造を出してないので、自分はこういう構造で試しましたよということがわかるようにしただけですね。
    > usersテーブルなのにuser_idは構造としてどうなのでしょうか?
    何がおかしいと思うの?任意の名前をつけることができるんだからおかしくはないでしょう。

    キャンセル

  • 2015/08/01 21:21

    ご返信ありがとうございます。

    > 手元で試すだけだからそうしただけです。
    。。。

    > 何がおかしいと思うの?任意の名前をつけることができるんだからおかしくはないでしょう。

    いえ、単純に上記のSQL文のusers.user_idという表記に違和感を感じた為、質問させて頂きました。
    users.id
    ならuserの一意のIDなんだろうなぁ。とわかるかと思うのですが
    users.user_idだと頭痛が痛いみたいな表記になってしまってなんだか気になるなと思い
    何か理由があれば教えて頂けたらなと思って質問させて頂きました。
    ありがとうございます。

    キャンセル

  • 2015/08/01 22:01

    まあ、基本的に自作のフレームワークではそうしている、というより、そうしないといけないフレームワークにしています。
    フィールド名だけでもどのテーブルを参照するのかがわかるようにしていますね。モデルを実装するときに、プロパティとフィールド名は共通の表記にすることでますを防ぐという目的もあってそうしています。

    キャンセル

  • 2015/08/01 22:04 編集

    追記

    cakephpは確かid出なくてはいけないとかあったはず…設計思想の問題でしょうね。

    キャンセル

  • 2015/08/01 23:39 編集

    ご回答ありがとうございます。

    > フィールド名だけでもどのテーブルを参照するのかがわかるようにしていますね。
    これが理由で、postsにuser_idというカラムを使用するというのはわかるのですが。。。
    例えばusersテーブルをAS句でAに変更して A.idにしたとして
    A.post_idだ!参照するテーブルはpostsだな!とはなると思いますが
    A.user_idだ!参照するテーブルはpostsだな!とはならないと思います。

    > モデルを実装するときに、プロパティとフィールド名は共通の表記にすることでますを防ぐという目的もあってそうしています。

    ?
    プロパティとはPHPのメンバ変数の事ですか?
    DBから取得した時に$data->idとなっているのが気に入らないならSQL文のAS句でuser_idにしてしまえば良いだけではないかなとも思いますが。

    色々な設計思想があるんですね。

    キャンセル

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

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

関連した質問

  • 解決済

    Java iBatisでSQLの共通部分を共有したい

    iBatisのSQL記述について質問です。 検索条件の部分が全く同じSQLが複数あるのですが、 修正の度に対象SQLの検索条件を全て書き直す必要があるため できれば検索条件の部分は

  • 解決済

    oracle SQL

    表のデータに「あ」から「ん」までの50音を使って 重複無くデータの更新をしたいです。 あ100 あ102 い102 あ103 あ104 い104 う104 え104 見たいな感じで

  • 解決済

    Hive 入れ子とカウント

    [hive] プログラミング初心者です。 select     a, count(a) from     table_a where     ver='2015-01-01' g

  • 解決済

    あるテーブルからid単位で集計したい

    あるテーブルにユニークでないidがあったとき この件数を取得したいのですが いまいちわかりません 下のsqlだとただテーブルの全件がとれるだけです。。。 select count(

  • 解決済

    重量に対する送料をSELECTするクエリー文

    MySQLで重量に対する送料をテーブルからSELECTするクエリーを考えているのですが、なかなか思いつかないのでアドバイスを頂けたらと思い質問させて頂きました。 まず重量に対する

  • 解決済

    SQLの書き方について

    基本的(なのかな?)な、SQL(MySQL)の書き方について教えてください。 UPDATE table_a SET field_a=123456/( SELECT count

  • 解決済

    SQLのCOUNTカラムに順位をつけるには

    表題のとおり、投票結果を集計したものに順位を付けたいです。 どのようにすべきでしょうか。 調べてみると、遅い早いで複数のアプローチがあるようですが、おすすめの方法をご教示いただけれ

  • 解決済

    指定した条件に、指定件数以上該当するデータを持つグループの取得。

    前提・実現したいこと 指定した条件に、指定件数以上該当するデータを持つグループの取得方法を教えていただきたいです。 例として、 orderテーブルにおいて、statusが200

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

  • SQL

    2330questions

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