結果セットから指定した数の行のみが必要な場合、結果セット全体をフェッチして、余分なデータを破棄するのではなく、クエリーで LIMIT 句を使用します。

MySQL は LIMIT row_count 句があり HAVING 句のないクエリーを最適化することがあります。

複数の行の ORDER BY カラムに同一の値がある場合、サーバーは自由にそれらの行を任意の順序で返しますが、その実行は実行プラン全体によって異なることがあります。 言い換えると、それらの行のソート順序は、順序付けされていないカラムに関して決定的ではありません。

実行プランに影響する 1 つの要素は LIMIT であるため、LIMIT を付けるか付けないかで ORDER BY クエリーは異なる順序で行を返すことがあります。 category カラムによってソートされるが、id および rating カラムに関して非決定的である次のクエリーを考慮します。

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

LIMIT を含めると、各 category 値内の行の順序に影響することがあります。 たとえば、これは有効なクエリー結果です。

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

各ケースで、行は ORDER BY カラムによってソートされますが、SQL 標準で必要とされるのはこれだけです。

LIMIT を使用してもしなくても同じ行順序を確保することが重要な場合は、ORDER BY 句に順序を決定的にする追加カラムを含めます。 たとえば、id 値が一意の場合、次のようにソートすることで、特定の category 値の行を id の順序で表示できます:

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

ORDER BY または GROUP BY と LIMIT 句を含むクエリーの場合、オプティマイザは、クエリーの実行速度を上げるために、順序付けられたインデックスをデフォルトで選択しようとします。 MySQL 8.0.21 より前は、他の最適化を使用する方が高速であっても、この動作をオーバーライドする方法はありませんでした。 MySQL 8.0.21 以降では、optimizer_switch システム変数 prefer_ordering_index フラグを off に設定することで、この最適化をオフにできます。

例: まず、次に示すように、テーブル t を作成して移入します:

# Create and populate a table t:

mysql> CREATE TABLE t (
    ->     id1 BIGINT NOT NULL,
    ->     id2 BIGINT NOT NULL,
    ->     c1 VARCHAR(50) NOT NULL,
    ->     c2 VARCHAR(50) NOT NULL,
    ->  PRIMARY KEY (id1),
    ->  INDEX i (id2, c1)
    -> );

# [Insert some rows into table t - not shown]

prefer_ordering_index フラグが有効になっていることを確認します:

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

次のクエリーには LIMIT 句があるため、可能な場合は順序付けされたインデックスを使用する必要があります。 この場合、EXPLAIN 出力からわかるように、テーブルの主キーが使用されます。

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

次に、prefer_ordering_index フラグを無効にし、同じクエリーを再実行します。今回は、インデックス i (WHERE 句で使用される id2 カラムを含む) と filesort を使用します:

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

セクション8.9.2「切り替え可能な最適化」も参照してください。