MySQL Order by index optimization

In some cases, MySQL can use the index directly to satisfy an order BY or GROUP by clause without having to do extra sorting. Although an order by is not exactly the same as an index, an index can be used, as long as the unused index portion and all the extra orders by fields are included in the WHERE clause.

Use the indexed MySQL order by

Several of the following queries use the index to resolve the order by or GROUP 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 WHERE key_part1=constant GROUP 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 with no index

In other cases, MySQL cannot use an index to satisfy an order by, although it uses the index to find records to match the WHERE clause. These situations are as follows:

* The different index keys are made by:

SELECT * FROM t1 ORDER BY key1, key2;

* In the noncontiguous index key section, do ORDER by:

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

* Both ASC and DESC are used:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* The index keys used to search for records are not the same as the order by:

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

* There are many tables connected together, and the fields in the read record are not all from the first very many tables (that is, the connection type of the first table in the result of EXPLAIN analysis is not const).

* Different order BY and GROUP by expressions are used.

* Records in the table index are not stored sequentially. For example, the HASH and HEAP tables are like this.

By executing EXPLAIN SELECT ... By, you know whether MySQL uses the index in the query. If the value of the Extra field is a using Filesort, MySQL cannot use the index. For more information, see "7.2.1 EXPLAIN Syntax (get information about a SELECT)". When the result must be sorted, MySQL 4.1 used the following filesort algorithm:

1. Read the records according to the index key, or scan the data table. Records that cannot match the WHERE clause are skipped.

2. Each record in the buffer is stored with a ' pair ' of 2 values (index key and record pointer). The size of the buffer depends on the value of the system variable sort_buffer_size.

3. When the buffer is slow, run qsort (Quick sort) and store the results in a temporary file. Save the stored block pointer (if all of the ' pair ' values are saved in the buffer, you do not need to create a temporary file).

4. Perform the above operation until all the records are read out.

5. Make a multiple merge and save blocks of up to Mergebuff (7) in another temporary file. Repeat this until all the blocks in the first file are placed in the second file.