• notice
  • Congratulations on the launch of the Sought Tech site

Method and optimization details of mysqlorderby statement

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

The code is as follows:

SELECT column_name(s)
FROM table_name
ORDER BY column_name

Example

SQL creation code:

The 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..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 ORDER BY statement optimization through index optimization:

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

The code is as follows:

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

Create an index on the [sort] field to achieve order by optimization using the index.

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

The code is 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, such as the following statement, the index cannot be used to optimize order by

The code is as follows:

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

3, WHERE+ multiple fields ORDER BY

The code is as follows:

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

Building 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 the ORDER BY clause without additional sorting.The where condition and order by use the same index, and the order by order is the same as the index order, and the fields of the order by are in ascending or descending order.

For example: the following SQL can use the index.

The 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, the index is not used in the following cases:

The code is as follows:

①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
--The field of order by mixes ASC and DESC
②SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
--The key used to query the row is different from the one used in ORDER BY
③SELECT * FROM t1 ORDER BY key1, key2;
--Use ORDER BY for different keywords:

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+