How does MySQL optimize the ORDERBY sorting statement bitsCN.com using indexes?
MySQL indexes are usually used to increase the search speed of data rows matching other tables when the WHERE condition is matched or when the join operation is performed.
MySQL can also use indexes to quickly sort and group order by and group by statements.
Optimize the order by statement of MySQL through index optimization:
1. order by index optimization. If an SQL statement is like:
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:
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.
SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] IN ([value1], [value2],…) Order by [sort];
3. WHERE + multiple fields ORDER
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.
MySQL Order By cannot use indexes to optimize sorting
* Perform order by on different index keys: (create indexes for key1 and key2 respectively)
SELECT * FROM t1 order by key1, key2;
* Order by: (key_part1 and key_part2 are used to create a joint index; key2 is used to create an index)
SELECT * FROM t1 WHERE key2 = constant order by key_part2;
* Both ASC and DESC :( key_part1 and key_part2 are used to create a joint index)
SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;
* The Index key used for searching records and the order by statement are not the same: (key1 and key2 create indexes respectively)
SELECT * FROM t1 WHERE key2 = constant order by key1;
* If expressions (functions) are applied in the WHERE and order by columns, indexes cannot be used to optimize order.
SELECT * FROM t1 order by year (logindate) LIMIT 0, 10;
Note:
1> only one index can be used for one mysql Query at a time. If you want to use an index for multiple fields, create a composite index.
2> in the order by Operation, MySQL uses indexes only when the sorting condition is not a query condition expression.
This article from The CSDN blog, reproduced please indicate the source: http://blog.csdn.net/ryb7899/archive/2010/05/11/5580624.aspx
Low profile, high profile work
BitsCN.com