MySQL Order by statement usage and Optimization

MySQL Order By keyword is used to classify the data in the record.
MySQL Order By Keyword Classification
Order by keyword is used to classify the data in the record.

Copy codeThe Code is as follows:
SELECT column_name (s)
FROM table_name
Order by column_name

Example

SQL creation code:

Copy codeThe Code is as follows:
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 .jb51.net', 1 );
Insert into mysql_order_by_test (uid, name, sex) VALUES (2, 'Li si', 2 );
Insert into mysql_order_by_test (uid, name, sex) VALUES (3, 'wang Erma sub', 1 );

Optimize the order by statement of MySQL through index optimization:

1. order by index optimization. If an SQL statement is like:

Copy codeThe Code is as follows:
SELECT [column1], [column2],... FROM [TABLE] order by [sort];

You can create an index on the [sort] column to optimize order by using the index.

2. Optimize the index of WHERE + order by, for example:

Copy codeThe Code is as follows:
SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] = [value] order by [sort];

Create a joint index (columnX, sort) to optimize order.

NOTE: If columnX corresponds to multiple values, the following statement cannot optimize order by using indexes.

Copy codeThe Code is as follows:
SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] IN ([value1], [value2],…) Order by [sort];

3. WHERE + multiple fields ORDER

Copy codeThe Code is as follows:
SELECT * FROM [table] WHERE uid = 1 ORDER x, y LIMIT 0, 10;

Creating an index (uid, x, y) to optimize order by is much better than creating an index (x, y, uid ).

In some cases, MySQL can use an index to satisfy the order by clause without additional sorting. The where condition and order by condition use the same index, and the order by order is the same as the index order, and the order by field is both ascending or descending.

For example, the following SQL statements can use indexes.

Copy codeThe Code is as follows:
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 in the following cases:

Copy codeThe Code is as follows:
① SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;
-- Order by field mixing ASC and DESC
② SELECT * FROM t1 WHERE key2 = constant order by key1;
-- The keywords used to query rows are different from those used in order.
③ SELECT * FROM t1 order by key1, key2;
-- Use order by for different keywords: