MySQL ORDER BY Statement usage and optimization detailed

The MySQL order BY keyword is used to classify the data in the record.

MySQL ORDER BY keyword classified by keyword

The ORDER BY keyword is used to classify the data in a record.

The code is as follows	Copy Code
SELECT column_name (s) From table_name ORDER BY column_name

Example

SQL Creation Code:

The code is as follows

Copy Code

CREATE TABLE IF not EXISTS mysql_order_by_test ( UID Int (a) not NULL auto_increment, Name Char (not NULL), Sex tinyint (1) Not NULL, KEY UID (UID) ) Engine=myisam DEFAULT Charset=utf8 auto_increment=4; INSERT into Mysql_order_by_test (UID, name, Sex) VALUES (1, ' www.111cn.net ', 1); INSERT into Mysql_order_by_test (UID, name, Sex) VALUES (2, ' Dick ', 2); INSERT into Mysql_order_by_test (UID, name, Sex) VALUES (3, ' King two-pox ', 1);

By optimizing the index to achieve MySQL's ORDER BY statement optimization:

1. Index optimization by. If an SQL statement is shaped like this:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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

The code is as follows	Copy Code
SELECT [Column1],[column2],.... From [TABLE] WHERE [ColumnX] In ([Value1],[value2],...) Order By[sort];

3, where+ multiple fields order by

The code is as follows	Copy Code
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

In some cases, MySQL can use an index to satisfy an ORDER BY clause without needing an extra sort. The Where condition and order by use the same index, and the ordering by is the same as the index order, and the fields of the orders by are ascending or descending.

For example, the following SQL can use an index.

The code is as follows	Copy Code
SELECT * from T1-Key_part1,key_part2,...; SELECT * from T1 WHERE key_part1=1 order by Key_part1 DESC, Key_part2 DESC; SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 DESC;

However, indexes are not used in the following situations:

code is as follows	copy code
①select * from T1 to Key_part1 DESC, key_part2 ASC; --order by Field mixed ASC and DESC ②select * from t1 WHERE key2=constant O Rder by Key1 --the keyword used for the query row is not the same as used in order by ③select * from T1 ORDER by Key1, Key2; --Use an order for different keywords by: