8.3.1.11 ORDER BY Optimization

In some cases, MySQL can use an index to satisfy an
ORDER BY clause without doing any extra
sorting.

The index can also be used even if the ORDER
BY does not match the index exactly, as long as all
of the unused portions of the index and all the extra
ORDER BY columns are constants in the
WHERE clause. The following queries use the
index to resolve the ORDER BY part:

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;
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;

In some cases, MySQL cannot use indexes
to resolve the ORDER BY, although it still
uses indexes to find the rows that match the
WHERE clause. These cases include the
following:

You use ORDER BY on different keys:

SELECT * FROM t1 ORDER BY key1, key2;

You use ORDER BY on nonconsecutive
parts of a key:

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

You mix ASC and
DESC:

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

The key used to fetch the rows is not the same as the one
used in the ORDER BY:

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

You use ORDER BY with an expression
that includes terms other than the key column name:

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

You are joining many tables, and the columns in the
ORDER BY are not all from the first
nonconstant table that is used to retrieve rows. (This is
the first table in the
EXPLAIN output that does
not have a const join
type.)

You have different ORDER BY and
GROUP BY expressions.

You index only a prefix of a column named in the
ORDER BY clause. In this case, the
index cannot be used to fully resolve the sort order. For
example, if you have a
CHAR(20) column, but index
only the first 10 bytes, the index cannot distinguish
values past the 10th byte and a
filesort will be needed.

The type of table index used does not store rows in order.
For example, this is true for a HASH
index in a MEMORY table.

Availability of an index for sorting may be affected by the
use of column aliases. Suppose that the column
t1.a is indexed. In this statement, the
name of the column in the select list is a.
It refers to t1.a, so for the reference to
a in the ORDER BY, the
index can be used:

SELECT a FROM t1 ORDER BY a;

In this statement, the name of the column in the select list
is also a, but it is the alias name. It
refers to ABS(a), so for the reference to
a in the ORDER BY, the
index cannot be used:

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

In the following statement, the ORDER BY
refers to a name that is not the name of a column in the
select list. But there is a column in t1
named a, so the ORDER BY
uses that, and the index can be used. (The resulting sort
order may be completely different from the order for
ABS(a), of course.)

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

By default, MySQL sorts all GROUP BY
col1,
col2, ... queries as if
you specified ORDER BY
col1,
col2, ... in the query as
well. If you include an explicit ORDER BY
clause that contains the same column list, MySQL optimizes it
away without any speed penalty, although the sorting still
occurs. If a query includes GROUP BY but
you want to avoid the overhead of sorting the result, you can
suppress sorting by specifying ORDER BY
NULL. For example:

MySQL has two filesort algorithms for
sorting and retrieving results. The original method uses only
the ORDER BY columns. The modified method
uses not just the ORDER BY columns, but all
the columns referenced by the query.

The optimizer selects which filesort
algorithm to use. It normally uses the modified algorithm
except when BLOB or
TEXT columns are involved, in
which case it uses the original algorithm. For both
algorithms, the sort buffer size is the
sort_buffer_size system
variable value.

The original filesort algorithm works as
follows:

Read all rows according to key or by table scanning. Skip
rows that do not match the WHERE
clause.

For each row, store a pair of values (the sort key value
and the row ID) in the sort buffer.

If all pairs fit into the sort buffer, no temporary file
is created. Otherwise, when the sort buffer becomes full,
run a qsort (quicksort) on it in memory and write it to a
temporary file. Save a pointer to the sorted block.

Repeat the preceding steps until all rows have been read.

Do a multi-merge of up to MERGEBUFF (7)
regions to one block in another temporary file. Repeat
until all blocks from the first file are in the second
file.

Repeat the following until there are fewer than
MERGEBUFF2 (15) blocks left.

On the last multi-merge, only the row ID (the last part of
the value pair) is written to a result file.

Read the rows in sorted order using the row IDs in the
result file. To optimize this, read in a large block of
row IDs, sort them, and use them to read the rows in
sorted order into a row buffer. The row buffer size is the
read_rnd_buffer_size
system variable value. The code for this step is in the
sql/records.cc source file.

One problem with this approach is that it reads rows twice:
One time during WHERE clause evaluation,
and again after sorting the value pairs. And even if the rows
were accessed successively the first time (for example, if a
table scan is done), the second time they are accessed
randomly. (The sort keys are ordered, but the row positions
are not.)

The modified filesort algorithm
incorporates an optimization to avoid reading the rows twice:
It records the sort key value, but instead of the row ID, it
records the columns referenced by the query. The modified
filesort algorithm works like this:

Read the rows that match the WHERE
clause.

For each row, record a tuple of values consisting of the
sort key value and the columns referenced by the query.

When the sort buffer becomes full, sort the tuples by sort
key value in memory and write it to a temporary file.

After merge-sorting the temporary file, retrieve the rows
in sorted order, but read the required columns directly
from the sorted tuples rather than by accessing the table
a second time.

Using the modified filesort algorithm, the
tuples are longer than the pairs used in the original method,
and fewer of them fit in the sort buffer. As a result, it is
possible for the extra I/O to make the modified approach
slower, not faster. To avoid a slowdown, the optimizer uses
the modified algorithm only if the total size of the extra
columns in the sort tuple does not exceed the value of the
max_length_for_sort_data
system variable. (A symptom of setting the value of this
variable too high is that you should see high disk activity
and low CPU activity.)

If a filesort is done,
EXPLAIN output includes
Using filesort in the
Extra column.

Suppose that a table t1 has four
VARCHAR columns a,
b, c, and
d and that the optimizer uses
filesort for this query:

SELECT * FROM t1 ORDER BY a, b;

The query sorts by a and
b, but returns all columns, so the columns
referenced by the query are a,
b, c, and
d. Depending on which
filesort algorithm the optimizer chooses,
the query executes as follows:

For the original algorithm, sort buffer tuples have these
contents:

(fixed size a value, fixed size b value,
row ID into t1)

The optimizer sorts on the fixed size values. After sorting,
the optimizer reads the tuples in order and uses the row ID in
each tuple to read rows from t1 to obtain
the select list column values.

The optimizer sorts on the fixed size values. After sorting,
the optimizer reads the tuples in order and uses the values
for a, b,
c, and d to obtain the
select list column values without reading
t1 again.

For slow queries for which filesort is not
used, you might try lowering
max_length_for_sort_data to a
value that is appropriate to trigger a
filesort.

To increase ORDER BY speed, check whether
you can get MySQL to use indexes rather than an extra sorting
phase. If this is not possible, you can try the following
strategies:

Use less RAM per row by declaring columns only as large as
they need to be to hold the values stored in them. For
example, CHAR(16) is better than
CHAR(200) if values never exceed 16
characters.

Change the tmpdir system
variable to point to a dedicated file system with large
amounts of free space. The variable value can list several
paths that are used in round-robin fashion; you can use
this feature to spread the load across several
directories. Paths should be separated by colon characters
(“:”) on Unix and
semicolon characters (“;”)
on Windows, NetWare, and OS/2. The paths should name
directories in file systems located on different
physical disks, not different
partitions on the same disk.