Mysql uses indexes to optimize order sorting _ MySQL

Personal original address: http: wwwphpthinkingcomarchives433 in the database, we generally perform index operations on some fields, which can improve data query speed and database performance, such as orderby, indexes are required before groupby. First my personal original address: http://www.phpthinking.com/archives/433

In the database, we usually perform index operations on some fields, which can improve the data query speed and improve the database performance such as order by and group.

Please note that
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.
Index description

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;
MySQL supports many data types. selecting the appropriate data type to store data has a great impact on performance.

Generally, the following guiding principles can be observed:
(1) smaller data types are generally better: smaller data types usually require less space in the disk, memory, and CPU cache for faster processing.
(2) a simple data type is better: the processing overhead of integer data is smaller than that of characters, because the strings are more complex. In MySQL, the built-in date and time data types should be used instead of strings to store the time, and the IP addresses of integer data types should be used to store the time.
(3) Avoid NULL as much as possible: the column should be specified as not null unless you want to store NULL. In MySQL, it is difficult to query and optimize columns with null values, because they make the index and index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.