MySQL uses index to optimize order ordering

Personal original address:http://www.phpthinking.com/archives/433

In the database we usually index some of the fields, which can improve the query speed of the data, and improve the performance of the database like order by, the group by before the index needs.

First, we need to pay attention.
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.
Some arguments about indexing

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;
MySQL supports many data types, and choosing the right data type to store data has a significant impact on performance.

In general, you can follow some of the following guidelines:
(1) smaller data types are usually better : Smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster.
(2) simple data types are better : integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
(3) try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

MySQL uses index to optimize order ordering