よくあるブログのデータベースで、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と書けますね。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答4件
0
ベストアンサー
MySQLでしたら
コレで出して頂いた物よりはある程度速くなるんじゃないかなと思います。
SQL
1SELECT 2 U.*, 3 IFNULL(P.post_count, 0) AS cnt 4FROM 5 users AS U 6LEFT JOIN 7 (SELECT 8 COUNT(1) AS post_count, 9 user_id 10 FROM 11 posts 12 GROUP BY user_id ) AS P 13ON 14 U.id = P.user_id 15ORDER BY IFNULL(P.post_count, 0) DESC
また、INDEXの見直しや絞り込めるようならWHEREで絞り込んだ方が速くなると思います。
投稿2015/08/01 08:00
編集2015/08/01 08:05総合スコア775
0
このSQLが重いのは、SELECTやORDERでのサブクエリが相関サブクエリになっていて、抽出行数と同じ回数のサブクエリが実行されているためではないかと思います。
(確認してませんが、サブクエリ内のusers.idが行毎に異なるので恐らく)
EXPLAINで調べてみると具体的にどうなっているか分かるかも知れません。参考ページ
ではどうすれば良いかというと、nanndemoiikara氏と殆ど同じです。
こちらのサブクエリ実行回数は1回で済むので、抽出行数が増えれば増えるほど実行コストは(相関サブクエリに比べて)小さくなると思います。
(その代わりusers各行とのマッチングコストは増えますが前者に比べて微々たるものです。またこのコストはusers.idにindexを作成することで軽減できます。)
SQL
1SELECT U.*, IFNULL(P.post_count, 0) AS cnt 2FROM users AS U 3LEFT JOIN ( 4 SELECT COUNT(*) AS post_count,user_id 5 FROM posts 6 GROUP BY user_id 7 ) AS P ON U.id = P.user_id 8ORDER BY cnt DESC
order by の関数実行省いたのと、count()に変えただけです。
countはpostgresだと()の方が早かった(index使うから?)と思うのですが、mysqlだと(1)のほうが早いのかも知れません。
投稿2015/08/01 09:57
総合スコア2068
0
基本通りに書くのが一番速そうですけど、どうでしょうね。
SQL
1SELECT 2 users.* 3 , count(DISTINCT post_id) AS cnt 4FROM 5 users 6LEFT JOIN 7 posts 8ON 9 users.user_id = posts.user_id 10GROUP BY 11 users.user_id;
sql
1CREATE TABLE `users` ( 2 `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, 3 `user_name` varchar(11) DEFAULT NULL, 4 PRIMARY KEY (`user_id`) 5) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 6 7CREATE TABLE `posts` ( 8 `post_id` int(11) unsigned NOT NULL AUTO_INCREMENT, 9 `user_id` int(11) DEFAULT NULL, 10 PRIMARY KEY (`post_id`), 11 KEY `user_id` (`user_id`) 12) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
手元の環境で試してみたけど、レコード数が少ないので、他の方の回答でも大差ありませんでしたが、Explain してみてやはり基本通りの方がインでクスが適切に使われる様子ですね。
投稿2015/08/01 09:39
編集2015/08/01 09:58退会済みユーザー
総合スコア0
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2015/08/01 10:38 編集
退会済みユーザー
2015/08/01 11:07 編集
2015/08/01 12:21
退会済みユーザー
2015/08/01 13:01
退会済みユーザー
2015/08/01 13:05 編集
2015/08/01 14:40 編集
0
ちなみに、users, postsはそれぞれ何件で、遅いというのは具体的に何秒なのでしょうか?
SQLは単にこれでいいのではないかと思います。
SQL
1SELECT * 2 , (SELECT Count(*) 3 FROM posts 4 WHERE posts.user_id = users.id) cnt 5FROM users 6ORDER BY cnt DESC
そこはさておき、このような検索だったら、postsのuser_idにインデックスをつけるべきです。絶対に。
それはやっていますか?やっていないから遅い気がするのですが…。違うのかな。
あと、usersの件数が多いようなら、*など使わずに実際に使用するカラムだけを指定すべきです。それだけで読み込みバイト数が大幅に削減でき、速度改善につながります。
投稿2015/08/01 09:01
総合スコア902
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2015/08/01 09:38
2015/08/01 10:12
2015/08/01 12:34
2015/08/01 14:48
2015/08/01 16:24
2015/08/03 22:35
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2015/08/01 08:15