MySQLで複数のSELECT文を1文にまとめたい

質問

以下のようなテーブル table1 があります。
*******************
table1
*******************
Name, Kyouka, Score, ID
*******************
山田, 国語, 92, 10001
鈴木, 国語, 71, 10002
田中, 国語, 89, 10003
山田, 数学, 65, 10004
鈴木, 数学, 69, 10005
杉田, 英語, 96, 10006
山田, 英語, 63, 10007
田中, 英語, 76, 10008
...
*******************

カラムIDはPRIMARYを指定しています。
カラムScoreはインデックスを作成しています。

このテーブルで、「IDと教科」を指定したときに、以下の2つのデータを得たいと思っています。
(1) IDに対応する名前と、その教科中の順位
(2) その教科のレコード数

具体例としては、例えば「ID=10001、教科=国語」を指定したときに、「山田、3人中1位」というような情報が得たいです。
（「ID=10001、教科=英語」のようなおかしな組み合わせは指定しないようになっています。）

(1) は、以下のようにして名前と順位を得る事が出来ました。
SELECT Name,
(SELECT COUNT(*) + 1 FROM table1 b WHERE b.Score > a.Score and Kyouka='国語') AS rank
FROM table1 a
WHERE ID='10001'
ORDER BY Score DESC;

(2)は、以下のようにして特定の教科のレコード数を得る事が出来ました。
SELECT COUNT(*) FROM table1 WHERE Kyouka='国語';

しかし、実際にはレコード数が大量にあり、頻繁にこの検索を実行する予定なので、負荷が心配です。
出来るだけサーバーの負荷を減らすために、1回のSELECT文の実行で(1)と(2)をどちらも実現できるような方法はないでしょうか。

また、このテーブルでインデックスを作成しているのはIDとScoreのみですが、より負荷を減らすにはKyoukaにもインデックスを作成した方が良いでしょうか。

何卒、よろしくお願いいたします。

回答No.3

root139 · ベストアンサー

教科中の順位とその教科のレコード数を1回で取得するなら、下記の様にSELECT句内のサブクエリを一つ追加すれば良いだけです。

----------------------------------------
SELECT
Name,
Kyouka,
Score,
(SELECT COUNT(*) + 1 FROM table1 b WHERE b.Score > a.Score AND a.Kyouka = b.Kyouka) AS rank,
(SELECT COUNT(*) FROM table1 c WHERE a.Kyouka = c.Kyouka) AS count
FROM table1 a
WHERE ID = '10001'
ORDER BY Kyouka, Score DESC;
----------------------------------------

また、上記の様に教科をサブクエリ内の結合条件で絞り込んでやると、WHERE句の条件を変えるだけで色々な絞込みが可能です。

例) [国語の全データを取得] -------------
SELECT
Name,
Kyouka,
Score,
(SELECT COUNT(*) + 1 FROM table1 b WHERE b.Score > a.Score AND a.Kyouka = b.Kyouka) AS rank,
(SELECT COUNT(*) FROM table1 c WHERE a.Kyouka = c.Kyouka) AS count
FROM table1 a
WHERE Kyouka = '国語'
ORDER BY Kyouka, Score DESC;
----------------------------------------

性能が気になるようでしたら、下記の様にカウントはFROM句内のサブクエリで取得した方がよいかも知れません。

----------------------------------------
SELECT
Name,
Kyouka,
Score,
(SELECT COUNT(*) + 1 FROM table1 b WHERE b.Score > a.Score AND a.Kyouka = b.Kyouka) AS rank,
c.count
FROM table1 a INNER JOIN
(SELECT Kyouka, COUNT(*) AS count FROM table1 GROUP BY Kyouka) c USING(Kyouka)
ORDER BY Kyouka, Score DESC;
----------------------------------------

なお、他の方も指摘されている通り、Kyoukaカラムにもインデックスを張ったほうが良いと思います。

お礼

とても分かりやすいご回答をいただけて大変感謝いたしております。
ありがとうございました。

回答No.2

Python864

負荷を減らすという事なら、この場合SQLを1回にするよりも、
(2)の教科のレコード数は、ID別に毎回取得する必要は無さそうに思いますので、
その負荷が無駄になるという理由から、
(2)教科のレコード数は複数のIDに対して一回だけ取得し、
プログラムの変数等に格納しておくほうが効率的だと思います。

いずれにしてもKyoukaにインデックスを作成したほうがよいです。

お礼

大変参考になりました。
ご回答ありがとうございました。

回答No.1

Bnbnbnta101

具体的に大量のデータがどのくらいあるのかわからないのでなんともいえないですが。

教科にはインデックスはったほうが良いです。

2は教科でグループバイして、教科毎のカウントを取得。

その結果を1のテーブル1と結合すれば一度のセレクトで取得できるでしょう。

お礼

参考にさせていただきます。
ご回答ありがとうございました。