Optimize the MySQL statement with ORDER by index

Optimize the MySQL statement with ORDER by index

Several guidelines for indexing:

1, reasonable indexing can speed up the efficiency of data reading, the unreasonable indexing will slow down the response speed of the database.

2, the more indexes, the slower the speed of updating data.

3, try to use MyISAM as the engine when using the index (because MySQL to btree storage index), rather than InnoDB. But MyISAM does not support transcation.

4, when your program and database structure/SQL statements have been optimized to the extent that can not be optimized, and program bottlenecks can not be resolved smoothly, that is, should consider the use of such as memcached such a distributed caching system.

5, habit and force yourself to use explain to analyze the performance of your SQL statements.

A very easy mistake to make:

Do not place an index on the selected field, which is meaningless. You should place the index reasonably on the statement that is selected for the condition, such as where,order by.

Example:

SELECT id,title,content,cat_id from article WHERE cat_id = 1;

The above statement, you put the index on the id/title/content is meaningless, there is no optimization for this statement. But if you put an index on the foreign key cat_id, the effect is quite large.

MySQL optimizations for several common order by statements:

1, order by + limit combination of index optimization. If an SQL statement is shaped like this:

SELECT [Column1],[column2],.... From [TABLE] order by [sort] LIMIT [offset],[limit];

This SQL statement optimization is simpler, and indexing is done on the [Sort] field.

2, where + order by + limit combination of index optimization, in the form of:

SELECT [Column1],[column2],.... From [TABLE] WHERE [COLUMNX] = [value] order by [sort] limit[offset],[limit];

This statement, if you still use the first example of the method of indexing, although the index can be used, but the efficiency is not high. A more efficient approach is to create a federated index (Columnx,sort)

3, where + in + ORDER by + limit combination of index optimization, such as:

SELECT [Column1],[column2],.... From [TABLE] WHERE [ColumnX] In ([Value1],[value2],...) Order By[sort] LIMIT [Offset],[limit];

This statement, if you use the method of indexing in the second example, will not get the desired effect (only on [sort] is the using index,where there is a using where;using filesort), on the grounds that there are several corresponding COLUMNX values.

How do you optimize this statement? I did not think of a good idea for the time being, and I saw a cheap offer on the internet, which was to split the statement into a union and then build the index in the second example:

SELECT [Column1],[column2],.... From [TABLE] WHERE [columnx]=[value1] ORDER by [sort] limit[offset],[limit] UNION SELECT [Column1],[column2],.... From [TABLE] WHERE [columnx]=[value2] ORDER by [sort] limit[offset],[limit] UNION ......

However, it has been proven that this method does not work at all, and that the efficiency is lower, and it is better to force the specified use of the sort index for most applications during testing.

4, do not apply the expression (function) on the field where and order by, for example:

SELECT * from [table] to year (date) LIMIT 0, 30;

5, Where+order by multiple field +limit, such as

SELECT * from [table] WHERE uid=1 order x,y LIMIT 0, 10;

For this statement, you might add one such index (X,Y,UID). But actually the better effect is (uid,x,y). This is caused by the mechanism by which MySQL handles sorting.

When you use the above example in the actual project, don't forget to look at the effect with explain after adding the index.