Shulou(Shulou.com)05/31 Report--

MySQL how to use the index to optimize ORDER BY sorting statements, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

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

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

Order by optimization can be achieved by building an index on the [sort] field.

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

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

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

Note: if columnX corresponds to multiple values, such as the following statement, you cannot use the index 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 LIMIT 0ml10

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

Situations where MySQL Order By cannot use indexes to optimize sorting

* do ORDER BY for different index keys: (key1,key2 index separately)

SELECT * FROM T1 ORDER BY key1, key2

* ORDER BY on discontiguous index keys: (key_part1,key_part2 builds federated index; key2 builds index)

SELECT * FROM T1 WHERE key2=constant ORDER BY key_part2

* use both ASC and DESC: (key_part1,key_part2 to build a federated index)

SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 ASC

* the index key used to search for records is not the same as that used for ORDER BY: (key1,key2 builds indexes separately)

SELECT * FROM T1 WHERE key2=constant ORDER BY key1

* if you apply expressions (functions) to WHERE and ORDER BY fields, you cannot use indexes to optimize order by

SELECT * FROM T1 ORDER BY YEAR (logindate) LIMIT 0Magne10

Special hint:

1 > mysql can only use one index at a time. If you want to use an index on multiple fields, create a composite index.

2 > in the ORDER BY operation, MySQL uses the index only if the sort condition is not a query condition expression.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.