limit句でoffset値を指定したのに検索結果が遅くなってしまうのは何が原因でしょうか？

Question

ページネーションで10件ごとにユーザ情報を閲覧できるサイトを作ろうとしています。
100万人のユーザデータが格納されたテーブルに対してoffset(＝ページ数)を指定してSELECT文でlimit件数10をかけたので10件だけを取得するだけなので早く処理できると思ったらすごく時間が掛かってしまいました。なぜなのでしょうか？こちらはMySQL5.5系+Linux環境です。

SELECT * FROM user_info WHERE (省略) LIMIT 1000, 10;

Answer 1

まず、MySQLの負荷対策をする上でEXPLAINを流すことが肝要です。
これにより発行したクエリのインデックス等を調べる事が出来ます。

EXPLAIN SELECT * FROM user_info WHERE (省略) LIMIT 1000, 10;

おそらく質問者のクエリは、type値がALLになっているのではないでしょうか？
これは全件検索を行っているという意味で1000件を検索しているために処理に時間がかかっているのでしょう。
これを10件だけ検索をさせるためには工夫が必要です。

まず、limit句のoffset値は使わないことです。
そして、where句に主キーとなるカラム値の次の項番から10件となるような条件を追加します。

例えば、1051番目のユーザから1060番目のユーザの情報を取得したい場合以下のようなSQLになります
(idカラムをAUTO INCREMENTされた主キーとします)。

SELECT * FROM user_info WHERE id > 1050 AND (省略) LIMIT 10;