Usage and optimization of MySQL Order by statement

MySQL Order By keyword is used to categorize data in records.
MySQL Order By Keyword
ORDER BY keyword is used to categorize data in records.

SELECT column_name(s) 
FROM table_name 
ORDER BY column_name

example

SQL creation code:

CREATE TABLE IF NOT EXISTS mysql_order_by_test (
  uid int(10) NOT NULL AUTO_INCREMENT,
  name char(80) 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.ofstack.com', 1);
INSERT INTO mysql_order_by_test (uid, name, sex) VALUES(2, ' li 4', 2);
INSERT INTO mysql_order_by_test (uid, name, sex) VALUES(3, ' The king 2 mike ', 1); 

MySQL ORDER BY statement optimization through index optimization:

1. Index optimization of ORDER BY. If an SQL statement looks like:

SELECT [column1],[column2], ... . FROM [TABLE] ORDER BY [sort];

The order by optimization can be achieved by building an index in the [sort] field.

2. Index optimization of WHERE + ORDER BY, as follows:

SELECT [column1],[column2], ... . FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

Create a joint index (columnX,sort) to achieve order by optimization.

Note: if columnX corresponds to multiple values, as shown below, indexes cannot be used to optimize order by

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;

Indexing (uid,x,y) to optimize order by is much better than indexing (x,y,uid)

In some cases, MySQL can use 1 index to satisfy the ORDER BY clause without additional sorting. The where condition USES the same index as order by, and order by is in the same order as the index, and the fields of order by are in ascending or descending order.

For example, the following sql can be indexed.

    SELECT * FROM t1 ORDER BY 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 when:

 1. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ; 
--order by Field mix of ASC and DESC
 2. SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ; 
-- Used to query the row keywords and ORDER BY Different ones are used in 
 3. SELECT * FROM t1 ORDER BY key1, key2 ; 
-- Use different keywords ORDER BY :