A basic tutorial on using indexes to sort data in Mysql _mysql

In MySQL, there are two ways to generate an ordered result set: one is using Filesort, and the other is scanning in indexed order. Sorting with an index is very fast, and you can use the same index to find and sort operations at the same time. Indexes can be sorted when the order of the indexes is the same as the column in order by and all columns are in the same direction (in all ascending or descending order). If the query is connected to more than one table, the index is used only if all columns in the order by are columns of the first table. Other situations will use Filesort.

A MySQL index is usually a search speed that is used to increase the data rows that match a where condition or to match other tables when performing a join operation.
MySQL can also use indexes to quickly perform the sorting and grouping operations of the order by and group by statements.
By optimizing the index to achieve MySQL's ORDER BY statement optimization:

CREATE TABLE actor (

actor_id int unsigned not NULL auto_increment,

name   varchar (?) NOT null DEFAULT ',

Password    varchar () not NULL DEFAULT ',

PRIMARY key (actor_id),

 key   (name)

Engine=innodb

INSERT INTO actor (Name,password) VALUES (' cat01 ', ' 1234567 ');

Insert into actor (Name,password) VALUES (' cat02 ', ' 1234567 ');

Insert into actor (Name,password) VALUES (' ddddd ', ' 1234567 ');

Insert into actor (Name,password) VALUES (' aaaaa ', ' 1234567 ');

Mysql> Explain select actor_id from actor order by actor_id \g

1. Row ***************************

      id:1

 select_type:simple

    table:actor

     type:index

possible_ Keys:null

     key:primary

   key_len:4

     ref:null

     rows:4 extra:using

    index

1 row in Set (0.00 sec )

Mysql> Explain select actor_id from actor order by password \g

1. Row ***************************

      id:1

 select_type:simple

    table:actor

     type:all

possible_ Keys:null

     key:null

   key_len:null

     ref:null

     rows:4 extra:using filesort

1 row in set (0. SEC)

Mysql> Explain select actor_id from actor order by name \g

1. Row ***************************

      id:1

 select_type:simple

    table:actor

     type:index

possible_ Keys:null

     key:name

   key_len:18

     ref:null

     rows:4 extra:using

    index

1 row in Set (0.00 sec)

Here's a list of some of the most common index orfer by optimizations:

1. If an SQL statement is shaped like this:

SELECT [Column1],[column2],.... From [TABLE] order by [sort];

Indexing on the [Sort] field enables the use of an index for order by optimization.
2, where + order by index optimization, such as:

SELECT [Column1],[column2],.... From [TABLE] WHERE [COLUMNX] = [value] order by [sort];

Establish a federated index (Columnx,sort) to implement order by optimization.
Note: If COLUMNX corresponds to multiple values, the following statement cannot use the index to implement an order by optimization

SELECT [Column1],[column2],.... From [TABLE] WHERE [ColumnX] In ([Value1],[value2],...) Order By[sort];

3, where+ multiple fields order by

SELECT * from [table] WHERE uid=1 order x,y LIMIT 0, 10;

Establishing an index (UID,X,Y) to implement an order by optimization is much better than establishing a (X,Y,UID) index.
MySQL order by cannot use index to optimize sorting
* The different index keys do ORDER BY: (Key1,key2 index)

SELECT * from T1-Key1, Key2;

* Make an ORDER by: (Key_part1,key_part2 to establish a federated index; Key2 index) on a noncontiguous index key section

SELECT * from T1 WHERE key2=constant order by Key_part2;

* Simultaneous use of ASC and DESC: (Key_part1,key_part2 to establish a federated index)

SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 ASC;

* The index keys used to search for records are not the same as those made by: (Key1,key2 indexed separately)

SELECT * from T1 WHERE key2=constant order by Key1;

* If an expression (function) is applied on the field of the Where and order by, the index cannot be used to implement an order by optimization

SELECT * from T1 (logindate) LIMIT 0, 10;

When MySQL can not use the index to sort, it will use its own sorting algorithm (fast sorting algorithm) in the memory (sort buffer) to sort the data, if the memory is not loaded, it will block the data on the disk, and then the individual data blocks sorted, Each block is then merged into an ordered result set (in fact, it is sorted out). There are two kinds of sorting algorithms for Filesort,mysql.
1. Two-pass scanning algorithm (two passes)
The way to do this is to first remove the fields that need to be sorted and the pointer information that can be positioned directly to the relevant row data, and then sort through the set of memory (set by the parameter sort_buffer_size), and then, after sorting through the row pointer information, remove the desired columns.
Note: This algorithm is an algorithm used before 4.1, it requires two access to data, especially the second read operation will lead to a large number of random I/O operations. On the other hand, the memory overhead is small.
2. First scan algorithm (single pass)
The algorithm takes out all the required columns at once, and then outputs the results directly after sorting in memory.
Note: The algorithm is used from the MySQL version 4.1. It reduces the number of I/O and is more efficient, but also has a large memory overhead. If we take out the columns that we don't need, we can greatly waste the memory needed for the sorting process. In the version after MySQL 4.1, you can set the Max_length_for_sort_data parameter to control the MySQL selection of the first sorting algorithm or the second type. When the total size of all large segments is larger than the Max_length_for_sort_data setting, MySQL chooses to use the first sort algorithm, whereas the second one is selected. In order to improve the sorting performance as much as possible, we naturally prefer the second sort algorithm, so it is necessary to simply remove the required Columns in Query.

When sorting a JOIN operation, if order by refers only to the column of the first table, MySQL filesort the table and then connects, at which point the explain output "Using filesort"; MySQL must generate a temporary table for the result set of the query, and perform filesort operations after the connection completes, at which point the explain output "Using temporary;" Using Filesort ".