MySQLの limit offset をより効果的に使う + 同点順位の求め方

MySQLのSELECT ステートメントでのLIMIT 節は
便利なので良く使っているが、
これは以外に性能を落とす原因となりうる。

ここでは、試験の得点成績の例の中で
同点順位の求め方を含めて
説明していく。

MySQLより引用
<<<<<
引数が 1 つの場合、その値は、戻り値として返す、
結果セットの冒頭からのレコード数を表す。

引数が 2 つの場合、最初の引数は戻り値として返す
最初のレコードまでのオフセットを表し、
2 つ目の引数は戻り値として返す最大レコード数を表す。

最初のレコードのオフセット値は 0（1 ではない）。
>>>>>

例えば、学校の生徒名簿があるとして
一年三組の成績10位-19位を求めるなら
SQL文は、

SELECT * FROM 生徒名簿 WHERE 学年 = 1 AND クラス = 3 ORDER BY 成績 LIMIT 9,10

である。

性能を落とす原因は、

- 最終結果セットを作る前の中間セットのレコードが大量である
  (クラスの人数が100万人とか、、、)
- 中間セットをORDER BYで並べ替えしている
  (成績順とか)

などである。

------------------------------------------------------

性能確保のためには、
中間セットを早い段階で
WHERE 節で絞り込むことが大切である

------------------------------------------------------

アイデア1 - 学年とクラス番号を組にした、インデックスを作る。

これは、簡単で極めて効果的である。

------------------------------------------------------

アイデア2 - 成績の順位をレコードに記す。

もし頻繁に成績を参照し(つまりLIMIT 節のSQLを使う頻度が高い)、
成績の更新頻度が稀ならば
成績の順位をレコードに記しておくことがよい。

レコードソート対象数(今回の例ならクラスの人数)が
100を超えたときに検討の価値有りと推測する。

そうすると、ソート対象レコードが極小になり時間節約、つまり、性能確保となる。

つまりこんなSQLになる

SELECT * FROM 生徒名簿 WHERE 学年 = 1 AND クラス = 3
 WHERE 10 <= クラス順位 AND クラス順位 <= 19 ORDER BY クラス順位

で、クラス順位をどのように成績でつけるかが問題である。

クラス順位をレコードに記す方法はいろいろあるだろうが、
ユーザ変数を使う方法が便利である。

SET @srank = 0 ; UPDATE 生徒名簿 SET クラス順位 = @srank := @srank + 1 WHERE 学年 = 1 AND クラス = 3 ORDER BY 成績 DESC

成績は同点の場合がありうるので、クラス順位にはクラス順位と同点順位がありうる。
同点順位をきちんとつけるには、頭をつかう必要がある。
次の例を参考にして欲しい。

SET @pre_point = 100, @rank = 1, @srank = 0;
 UPDATE 生徒名簿
 SET
   同点順位 =
     if(@pre_point = 成績,
        @rank,
        @rank := @srank + 1 + (@pre_point := 成績) * 0) ,
   クラス順位 = @srank := @srank + 1
 ORDER BY 成績 DESC, 生徒番号

--------------------------------------
頭をつかうところは、
   + (@pre_point := 成績) * 0)
の部分である
--------------------------------------