(mysql.info) order-by-optimization
Info Catalog
(mysql.info) outer-join-simplification
(mysql.info) query-speed
(mysql.info) group-by-optimization
7.2.12 `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;
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 non-consecutive 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 are joining many tables, and the columns in the `ORDER BY' are
not all from the first non-constant 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.
* 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.
With `EXPLAIN SELECT ... ORDER BY', you can check whether MySQL can use
indexes to resolve the query. It cannot if you see `Using filesort' in
the `Extra' column. See explain.
A `filesort' optimization is used that records not only the sort key
value and row position, but the columns required for the query as well.
This avoids reading the rows twice. The `filesort' algorithm works like
this:
1. Read the rows that match the `WHERE' clause.
2. For each row, record a tuple of values consisting of the sort key
value and row position, and also the columns required for the
query.
3. Sort the tuples by sort key value
4. 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.
This algorithm represents a significant improvement over that used in
some older versions of MySQL.
To avoid a slowdown, this optimization is used 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 you want 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:
* Increase the size of the `sort_buffer_size' variable.
* Increase the size of the `read_rnd_buffer_size' variable.
* Change `tmpdir' to point to a dedicated filesystem with large
amounts of empty space. This option accepts several paths that are
used in round-robin fashion. Paths should be separated by colon
characters (‘`:'’) on Unix and semicolon characters
(‘`;'’) on Windows, NetWare, and OS/2. You can use this
feature to spread the load across several directories. _Note_: The
paths should be for directories in filesystems that are located on
different _physical_ disks, not different partitions on the same
disk.
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 `ORDER BY' clause explicitly 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:
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
Info Catalog
(mysql.info) outer-join-simplification
(mysql.info) query-speed
(mysql.info) group-by-optimization
automatically generated byinfo2html