MySQLのlimitの使い方について、サンプルSQLを紹介しながらまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
MySQLのlimitの構文
SQLにlimitを指定することで、一度に取得する件数を指定できます。
SELECT * FROM employees LIMIT 10
上記のSQLは、employees（社員）テーブルから、10件のデータを取得します。
実行するとこうなります。
【関連記事】
▶SQL limitを使ったページング、ランダム抽出、速度改善をおこなう
offset（開始位置）の指定方法
limit m,nと指定することで、m件をoffset（開始位置）として、n件を取得するという指定が可能。
SELECT * FROM employees LIMIT 10,5
実行するとこうなります。
employees（社員）テーブルから、10件オフセットにして、5件取得します。
直観では、「limit 10,5は 5件オフセットにして10件取得かな…」と思ってしまうのではないでしょうか？注意が必要です。
なお、offset指定はselect文で使うことができますが、delete文やupdate文で使用することができません。
MySQLのlimitの使い方
MySQLで、後ろからデータを取得
最新日付のデータを取得するには、order byとlimitを組み合わせます。
SELECT * FROM `employees` order by hire_date desc LIMIT 3
上記SQLは、employees（社員）テーブルのhire_date（雇用日）をorder byにdesc指定して降順にソートし、データを3件取得します。
実行すると、こうなります。
MySQLでは、全件ソート後にlimitの指定件数を取得するのではなく、ソート結果からlimitで指定された件数が見つかったらすぐにソートを終了します。
このため、特にソート対象カラムにインデックスが使用されていた場合、比較的高速に処理を実行することができます。
全件数カウントを短時間で取得する方法
count(*)を実行してテーブル件数を取得すると、テーブルフルスキャンとなるため、件数の多いテーブルではレスポンス時間が問題になるケースがあります。
以下のSQLは、件数10,000以下なら全件数を、件数10,000以上なら10,000を取得します。
SELECT count(tmp_id) as COUNT FROM ( SELECT emp_no as tmp_id FROM employees LIMIT 10000 ) as tmp_cnt
具体的には、employees（社員）テーブルのemp_no（社員番号）をlimitで最大10,000件取得するサブクエリを実行し、メインクエリで件数をカウントしています。
実行すると、「Query took 0.0032 seconds.」と出力され、COUNT 10,000を取得します。
employeesの件数は約30万件ですが、この方法なら件数がいくら増えても上記クエリのレスポンス時間はあまり変わりません。
また、MySQLには、SQL_CALC_FOUND_ROWSと情報関数FOUND_ROWS()で件数を取得する仕組みがあります。
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.14 情報関数
しかし、SQL_CALC_FOUND_ROWSを使った場合でもテーブルフルスキャンが実行されます。
select SQL_CALC_FOUND_ROWS * from employees limit 10
上記SQLをexplainして実行計画を確認すると、type=ALL（テーブルフルスキャン）となっています。
テーブル件数を取得する際、指定件数よりも多ければ「◯◯件以上」という情報で充分な場合、サブクエリ＋limitの組み合わせでレスポンス時間を制限するのが良いでしょう。
limit 0指定の意味はSQLの妥当性チェック
SQLにlimit 0を指定すると、データを1件も取得せずに即座にSQLを終了します。
デバッグ目的で、SQLの妥当性を確認するために使われるケースがあります。
SELECT first_name, last_name, birth_date FROM employees a WHERE birth_date = ( select min(birth_date) from employees b ) LIMIT 0
上記のSQLを実行すると、「返り値が空でした (行数 0)。 (Query took 0.0003 seconds.)」となります。
explainで実行計画を確認すると、プライマリクエリのtype=NULL、Extraには「Zero limit」と表示されています。
limit 0を削除して下記を実行すると、実行すると、「6 total, Query took 0.1798 seconds.」となり、6件のデータが取得されます。
SELECT first_name, last_name, birth_date FROM employees a WHERE birth_date = ( select min(birth_date) from employees b )
このSQLでexplainを実行すると、こうなります。
テーブルフルスキャン（type=ALL）が実行されているため、件数が多いほど処理に時間がかかるSQLになっていることを確認できます。
上記の場合、およそ600倍レスポンス時間に差が出ることになります。
MySQLのサブクエリでlimit使用時の制限
MySQLでは、limitステートメントをサブクエリをin演算子で使うことができません。
select * from employees where hire_date in ( select hire_date from employees order by hire_date limit 1 )
実行すると、「#1235 – This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’」というエラーが出力されます。
limitは、in、all、any、someを使ったサブクリには使えないんですね。
上記のケースであれば、「in」を「=」に変更することで実行可能になります。
select * from employees where hire_date = ( select hire_date from employees order by hire_date limit 1 )
employees（社員）テーブルから、hire_date（雇用日）が最も古い社員の情報を取得するSQLです。
実行すると、こうなります。
まとめ
- MySQLはlimitを使って、最大取得件数とオフセット値を指定することができる
- 最新データを取得するには、order byとlimitを組み合わせる
- サブクエリ、limit、count関数を組み合わせると短時間で件数取得が可能なケースあり
- limit 0はSQLコードの妥当性チェックに使える
- MySQLではサブクエリ＋in、all、any、some組み合わせ時に制限あり
