The MySQL index is usually the search speed that is used to match the data rows of the Where condition, or when the join operation is performed, matching other tables.
MySQL can also use indexes to quickly perform ordering and grouping operations for order by and GROUP by statements.
The ORDER BY statement optimization for MySQL is achieved through index optimization:
1. Index optimization for ORDER by. If an SQL statement is like:
SELECT [Column1],[column2],.... From [TABLE] ORDER by [sort];
Indexing on the [Sort] field allows for an order by optimization using an index.
2, where + ORDER by index optimization, shape such as:
SELECT [Column1],[column2],.... From [TABLE] WHERE [COLUMNX] = [value] ORDER by [sort];
Establish a federated index (Columnx,sort) to implement order by optimization.
Note: If the columnx corresponds to more than one value, the index cannot be used to achieve an order by optimization, as in the following statement
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, y LIMIT 0, 10;
Indexing (Uid,x,y) achieves an order by optimization, which is much better than establishing a (X,Y,UID) index.
MySQL ORDER by cannot use an index to optimize the sorting situation
* ORDER by for different index keys: (key1,key2 indexed separately)
SELECT * from T1 ORDER by Key1, Key2;
* Do an ORDER by on a non-contiguous index key section: (Key_part1,key_part2 to establish a federated index; Key2 index)
SELECT * from T1 WHERE key2=constant ORDER by Key_part2;
* Both ASC and DESC are used: (Key_part1,key_part2 build Federated Index)
SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 ASC;
* Index keys for searching records are not the same as ORDER by: (Key1,key2 indexed separately)
SELECT * from T1 WHERE key2=constant ORDER by Key1;
* If an expression (function) is applied on the field of where and order by, the index cannot be used to achieve an order by optimization
SELECT * from T1 ORDER by year (Logindate) LIMIT 0, 10;
Special tips:
1>mysql only one index can be used for a query. If you want to use indexes on multiple fields, create a composite index.
2> in an order by operation, MySQL uses the index only if the sort condition is not a query condition expression.
How MySQL optimizes order by ordering statements with indexes
