How to use indexes to optimize MySQL ORDER BY statements

Create table & create index

create table tbl1 (
id int unique, sname varchar(50),
index tbl1_index_sname(sname desc)
);

Syntax for creating an index on an existing table

create [unique|fulltext|spatial] index index name on table name (field name [length] [asc|desc]);

MySQL can also use indexes to quickly perform sorting and grouping operations in ORDER BY and GROUP BY statements.

Optimize MySQL ORDER BY statements through index optimization:

1. ORDER BY index optimization

If a SQL statement is as follows:

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

By creating an index on the [sort] column, you can use the index to optimize order by.

2. Index optimization of WHERE + ORDER BY

Such as:

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, the following statement cannot use the index to optimize the 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,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

1. Perform ORDER BY on different index keys: (create indexes for key1 and key2 separately)

SELECT * FROM t1 ORDER BY key1, key2;

2. Perform ORDER BY on non-contiguous index key parts: (create a joint index on key_part1 and key_part2; create an index on key2)

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

3. Using both ASC and DESC: (key_part1, key_part2 create a joint index)

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

4. The index key used to search records is not the same as the one used for ORDER BY: (key1 and key2 are indexed separately)

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

5. If you apply expressions (functions) to WHERE and ORDER BY columns, you cannot use indexes to optimize the order by.

SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

Special Tips:

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

2> In an ORDER BY operation, MySQL uses the index only when the sort condition is not a query condition expression.

The above is the details of how MySQL uses indexes to optimize ORDER BY sorting statements. For more information about MySQL optimizing ORDER BY sorting statements, please pay attention to other related articles on 123WORDPRESS.COM!