MySQL optimizes statements with ORDER BY through indexes

A few guidelines for indexing:

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

2. The more indexes, the slower the data will be updated.

3. Try to use indexes with MyIsam as the engine (because MySQL stores indexes with BTree) instead of InnoDB. But MyISAM does not support Transcation.

4. When your application and database structure /SQL statements have been optimized to the point where they cannot be optimized, and the application bottleneck cannot be solved, it is time to consider using a distributed cache system such as memcached.

5. Get used to and force yourself to analyze the performance of your SQL statements using EXPLAIN.

1 easy mistake to make:

Do not place the index on the selected field; it is meaningless. The index should be properly placed on the conditional selection statement, such as where, order by.

Example:

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

In the above statement, it makes no sense for you to place an index on id/title/content, which has no optimization effect on the statement. But if you put an index on the foreign key cat_id, that's a pretty big deal.

MySQL optimizations for several common ORDER BY statements:

1. Index optimization of ORDER BY + LIMIT combination. If an SQL statement looks like:

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

This SQL statement optimization is simple, just index it in the [sort] field.

2. Index optimization of WHERE + ORDER BY + LIMIT combination, as follows:

SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort] LIMIT[offset],[LIMIT];

This statement, if you still use the indexing method in the first example, can be used, but it is not very efficient. A more efficient method is to create a joint index (columnX,sort)

3. Index optimization of WHERE + IN + ORDER BY + LIMIT combination, as follows:

SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] IN ([value1],[value2],...) ORDER BY[sort] LIMIT [offset],[LIMIT];

This statement will not have the desired effect if you use the indexing method in the second example (using index on [sort] only, using where on WHERE; using where on sort). using filesort), because there are multiple values for columnX.

How is this statement optimized? I haven't thought of any good method yet, but I have found a cheap one available on the Internet, which is to split this statement with 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 approach does not work at all and is less efficient, and that it is better to force the use of a sorted index for most applications when testing

4. Do not apply expressions (functions) to the fields of WHERE and ORDER BY, such as:

SELECT * FROM [table] ORDER BY YEAR(date) LIMIT 0,30;

5. WHERE+ORDER BY multiple fields +LIMIT, for example

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 MySQL's sorting mechanism.

When you apply the above example to a real project, don't forget to use EXPLAIN to see the effect after adding the index.