余分なソートを行わずに ORDER BY の要求に応じるために、MySQL はインデックスを使用する場合があります。

すべての使用されていないインデックス部分とほかの部分が WHERE 節内で定数であるカラムである場合、ORDER BY がインデックスに完全にマッチしない場合でもこのインデックスを使用できます。次のクエリーではインデックスを使用して ORDER BY 部分を解決します。

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1
  WHERE key_part1=constant
  ORDER BY key_part2;
SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
  WHERE key_part1=1
  ORDER BY key_part1 DESC, key_part2 DESC;

MySQL で ORDER BY の解決にインデックスを使用できない場合 (この場合も MySQL は WHERE 節の条件に一致するレコードの検索にインデックスを使用します)。これには、次のような場合が含まれます。

インデックスをソートに使用できるかどうかは、カラムエイリアスの使用によって影響を受けることがあります。カラム t1.a にインデックスが設定されていると仮定します。次のステートメントで、選択リスト内のカラム名は a です。これは t1.a を指しているため、ORDER BY 内での a の参照にはインデックスを使用できます。

SELECT a FROM t1 ORDER BY a;

次のステートメントでも、選択リスト内のカラム名は a ですが、これはエイリアスです。これは ABS(a) を指しているため、ORDER BY 内での a の参照にはインデックスを使用できません。

SELECT ABS(a) AS a FROM t1 ORDER BY a;

次のステートメントでは、選択リスト内のカラム名とは異なる名前を ORDER BY で参照しています。ただし、t1 には a というカラムがあるため、ORDER BY ではそれが使用され、インデックスは使用できます。(当然ながら、結果として ABS(a) の順序とはまったく異なるソート順序になります。)

SELECT ABS(a) AS b FROM t1 ORDER BY a;

MySQL はデフォルトで、GROUP BY col1、col2, ...の全クエリーを ORDER BY col1、col2, ...で指定したかのように、クエリーをソートします。同じカラムリストを含む ORDER BY 節を明示的に取り入れた場合、ソートが実行されるとはいえ、MySQL は速度ペナルティなしに最適化します。クエリーに GROUP BY が含まれていながら、結果のソートに費やすオーバーヘッドを避けたい場合、ORDER BY NULL を指定することでソートを実行しないようにすることができます。例 :

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

EXPLAIN SELECT ... ORDER BY を使用すると、MySQL でインデックスを使用してクエリーを解決できるかどうかを確認できます。Extra カラムに Using filesort が出力された場合は、MySQL で ORDER BY の解決にインデックスを使用できません。項4.2.1. 「EXPLAIN を使用して、クエリーを最適化する」 を参照してください。

MySQL には、結果をソートして取り出すための filesort アルゴリズムが 2 つあります。元のメソッドは ORDER BY のカラムだけを使用します。変更されたメソッドは、ORDER BY のカラムだけでなく、クエリーで使用されているすべてのカラムを使用します。

どちらの filesort アルゴリズムを使用するかはオプティマイザが選択します。通常は変更されたアルゴリズムが使用されますが、BLOB カラムや TEXT カラムが対象となる場合には元のアルゴリズムが使用されます。

元の filesort アルゴリズムは次のように動作します。

このアプローチの問題の 1 つは、行を WHERE 節の評価時に 1 回、値の組をソートしたあとにもう 1 回、合計 2 回読み取ることです。また、1 回目は行に連続してアクセスできても (テーブルスキャンを実行する場合など)、2 回目のアクセスはランダムになります。(ソートキーは順に並べ替えられますが、行の位置は変更されないため。)

変更された filesort アルゴリズムは、ソートキーの値と行の位置だけでなく、クエリーに必要なカラムも記録するように最適化されています。これにより行の 2 度読みを避けられます。変更された filesort アルゴリズムは次のように動作します。

変更された filesort アルゴリズムを使用すると、元のアルゴリズムで使用される組よりもタプルが長くなり、ソートバッファーに収まるタプル数は少なくなります (ソートバッファーのサイズは sort_buffer_size で指定される)。その結果、追加の I/O が発生して、変更されたアルゴリズムの速度は逆に低下する可能性があります。速度低下を避けるため、この最適化は、ソートタプル内の追加カラムの合計サイズが max_length_for_sort_data システム変数の値を超えない場合にかぎり使用されます。(この変数が高く設定されると、活発なディスクアク活動に対して低い CPU 活動といった状態が発生します)。

filesort が使用されていない低速のクエリーでは、filesort が発生するように max_length_for_sort_data を小さくしてみることもできます。

ORDER BY 速度を上げたい場合、MySQL が余分な並べ替えフレーズよりもインデックスを使用できるか確認してください。これが不可能な場合、次の手段を試してみてください。