В этом запросе индекс по (key_part1, key_part2) позволяет оптимизатору избежать сортировки:

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

Однако запрос использует SELECT *, который может выбрать больше столбцов, чем key_part1 и key_part2. В этом случае сканирование всего индекса и поиск строк в таблице для нахождения столбцов, отсутствующих в индексе, может быть дороже, чем сканирование таблицы и сортировка результатов. Если это так, оптимизатор, скорее всего, не будет использовать индекс. Если SELECT * выбирает только столбцы индекса, индекс используется, и сортировка избегается.

Если t1 является таблицей с первичным ключом, первичный ключ таблицы неявно входит в индекс, и индекс может быть использован для решения ORDER BY для этого запроса:

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

В этом запросе key_part1 является константой, поэтому все строки, к которым обращается через индекс, находятся в порядке key_part2, и индекс по (key_part1, key_part2) избегает сортировки, если условие WHERE достаточно селективно, чтобы сделать сканирование диапазона индекса дешевле, чем сканирование таблицы:

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

В следующих двух запросах, использование индекса аналогично тем же запросам без DESC, показанных ранее:

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

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

В следующих двух запросах key_part1 сравнивается с константой. Индекс используется, если условие WHERE достаточно селективно, чтобы сделать сканирование диапазона индекса дешевле, чем сканирование таблицы:

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

В следующем запросе ORDER BY не называет key_part1, но все выбранные строки имеют постоянное значение key_part1, поэтому индекс по-прежнему может использоваться:

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

Запрос использует ORDER BY на разных индексах:

SELECT * FROM t1 ORDER BY key1, key2;

Запрос использует ORDER BY на несмежных частях индекса:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

Запрос сочетает ASC и DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

Индекс, используемый для извлечения строк, отличается от того, который используется в ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Запрос использует ORDER BY с выражением, включающим термины, отличные от имени столбца индекса:

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

Запрос соединяет много таблиц, и столбцы в ORDER BY не все из первой неконстантной таблицы, которая используется для извлечения строк. (Это первая таблица в выводе EXPLAIN, у которой нет типа соединения const).

В запросе разные выражения ORDER BY и GROUP BY.

Существует индекс только на префиксе столбца, указанного в условии ORDER BY. В этом случае индекс не может быть использован для полного определения порядка сортировки. Например, если проиндексированы только первые 10 байтов столбца CHAR(20), индекс не может различать значения после 10-го байта, и требуется filesort.

Индекс не хранит строки в порядке. Например, это верно для индекса HASH в таблице MEMORY.

Увеличьте значение переменной sort_buffer_size. В идеале значение должно быть достаточно большим, чтобы весь результат поместился в буфер сортировки (чтобы избежать записи на диск и слияния проходов), но как минимум оно должно быть достаточно большим, чтобы вместить 15 кортежей. (До 15 временных файлов диска сливаются, и в памяти должно быть место как минимум для одного кортежа на файл.)

Учитывайте, что размер значений столбцов, хранящихся в буфере сортировки, зависит от значения системной переменной max_sort_length. Например, если кортежи хранят значения длинных строковых столбцов, и вы увеличиваете значение max_sort_length, размер кортежей буфера сортировки также увеличивается и может потребовать увеличения sort_buffer_size. Для значений столбцов, вычисленных в результате строковых выражений (например, тех, которые вызывают строковую функцию), алгоритм filesort не может определить максимальную длину значений выражений, поэтому он должен выделять max_sort_length байт для каждого кортежа.

Для отслеживания количества проходов слияния (для слияния временных файлов) проверьте системную переменную Sort_merge_passes.

Увеличьте значение переменной read_rnd_buffer_size, чтобы читать больше строк за один раз.

Измените системную переменную tmpdir на указание на специализированную файловую систему с большим объемом свободного места. Значение переменной может перечислять несколько путей, которые используются в режиме циклического обхода; вы можете использовать эту функцию для распределения нагрузки по нескольким каталогам. Разделяйте пути с помощью двоеточия (:) в Unix и точки с запятой (;) в Windows. Пути должны указывать на каталоги в файловых системах, расположенных на разных физических дисках, а не на разных разделах одного диска.

Если столбец Extra вывода EXPLAIN не содержит Using filesort, индекс используется, и filesort не выполняется.

Если столбец Extra вывода EXPLAIN содержит Using filesort, индекс не используется, и выполняется filesort.

<sort_key, rowid>: Это указывает, что кортежи буфера сортировки являются парами, которые содержат значение ключа сортировки и идентификатор строки исходной строки таблицы. Кортежи сортируются по значению ключа сортировки, а идентификатор строки используется для чтения строки из таблицы.

<sort_key, additional_fields>: Это указывает, что кортежи буфера сортировки содержат значение ключа сортировки и столбцы, на которые ссылается запрос. Кортежи сортируются по значению ключа сортировки, а значения столбцов читаются непосредственно из кортежа.

<sort_key, packed_additional_fields>: Подобно предыдущему варианту, но дополнительные столбцы упаковываются плотно вместе вместо использования кодирования с фиксированной длиной.