Detailed explanation of MySQL Order by statement optimization code

Order by statement is used for sorting. We often use Order by for sorting. Now I will tell you about the usage of Order by and optimized sorting. Students in need can refer to

MySQL Order By keyword is used to classify the data in the record.
MySQL Order By Keyword is classified according to keywords
ORDER BY keyword is used to classify the data in the record.

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.jb51.net', 1);
INSERT INTO mysql_order_by_test (uid, name, sex) VALUES(2, '李四', 2);
INSERT INTO mysql_order_by_test (uid, name, sex) VALUES(3, '王二麻子', 1);

Implement MySQL’s ORDER BY statement optimization through index optimization:

1 , ORDER BY index optimization. If a SQL statement is in the form:

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

Create an index in the [sort] field, you can use the index to perform order by optimization.

2. Index optimization of WHERE + ORDER BY, in the form:

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

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

Note: If column Creating an index (uid, x, y) to optimize order by is much better than building an index (x, y, uid)

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

For example: The following sql can use indexes.

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

But the index is not used in the following situations:

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

The above is the detailed content of Detailed explanation of MySQL Order by statement optimization code. For more information, please follow other related articles on the PHP Chinese website!