SQLが苦手な自分向けのメモ

遅い版

ユーザー毎user_idに合計値はtotal_score順にランキングを出力する例です。

SELECT user_id, total_score, (select count(total_score) FROM total_scores b WHERE a.total_score < b.total_score) + 1 rank FROM total_scores a ORDER BY rank ASC

... 動作検証した結果、一レコードずつcountするため凄く遅いです汗.
計算量ではO(N^2)的な？

count(total_score)の場合の結果

user_id	total_score	rank
1191	10000	1
1	9555	2
5235	9555	2
62	9553	4
987	2222	5

SELECT user_id, total_score, (select count(DISTINCT total_score) FROM total_scores b WHERE a.total_score < b.total_score) + 1 rank FROM total_scores a ORDER BY rank ASC

count(DISTINCT total_score)の場合の結果

user_id	total_score	rank
1191	10000	1
1	9555	2
5235	9555	2
62	9553	3
987	2222	4

参考

余談

最初total_scoreをSUM(score)で求めてたけど、あきらめてtotal_score_viewを作成しました(ビューの中でユーザー毎の合計値を算出)。

イマイチ合計値をビューで表現すべきなのか、（処理負荷的に）テーブルに格納して良いのか今のところ判断ツキマセヌ...が遅くなるのが目に見えます。処理負荷を検証してみたいところです。
（追記。自分の環境で、数千件で1秒を超える程度の遅さでした.4万レコードでは7分まっても結果がでませんでした汗）

おまけ

特定のランキングの範囲を指定する例

SELECT user_id, total_score, (select count(DISTINCT total_score) FROM total_scores b WHERE a.total_score < b.total_score) + 1 rank FROM total_scores a having rank Between 70 and 80
ORDER BY rank

早い版

コメントして頂いたSQLを参考にしたバージョンです。
（コメントして頂いたものは同順位を考慮していますが、ここでは考慮していません。）

どの程度早いかというと、自分の環境で4万レコードで50ms以下程度。
計算量でいうとO(N)っいう比較的早いやつ？

全順位取得

set @c=0;
select @c:=@c+1 as rank, user_id, @p:=total_score as total_score 
from total_scores
order by total_score desc;

指定ユーザーの順位を取得する

set @c:=0; 
SELECT tmp.user_id, tmp.total_score, tmp.rank rank FROM 
(SELECT user_id, total_score, @c:=@c+1 rank FROM total_scores ORDER BY total_score DESC) tmp 
WHERE user_id=100

指定した順位を取得する

3000〜3010位を取得

set @c:=0;
SELECT tmp.user_id user_id, tmp.total_score total_score, tmp.rank rank FROM 
(SELECT user_id, total_score, @c:=@c+1 rank FROM total_scores ORDER BY total_score DESC) tmp
ORDER BY rank asc
LIMIT 3000, 10;

※　LIMITではなくてbetweenでもいける気がしてきたけど、現在検証できないため、後日検証して差し替えます。