Optimize MySQL statements with ORDER BY by index

Several guidelines for indexing:

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

2, the more the index, the slower the update data.

3, try to use the index when using MyIsam as an engine (because MySQL uses BTree to store indexes), not InnoDB. But MyISAM does not support Transcation.

4, when your program and database structure / SQL statements have been optimized to the extent not optimized, and the program bottleneck can not be successfully resolved, it is time to consider the use of distributed caching systems such as memcached.

5, habits and forcing yourself to use EXPLAIN to analyze your SQL statement performance.

An easy mistake to make:

It's pointless not to place an index on the field of your choice. Should be placed on the conditions of the statement reasonable index, 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 that you place an index on id / title / content, without any optimization of this statement. But if you place an index on the foreign key cat_id, that's a pretty good thing.

Several commonly used ORDER BY statement of the MySQL optimization:

1, ORDER BY + LIMIT combination of index optimization. If a SQL statement shaped like:

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

This SQL statement optimization is relatively simple, in the [sort] this index can be established on the field.

2, WHERE + ORDER BY + LIMIT combination of index optimization, shaped as:

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

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

3, WHERE + IN + ORDER BY + LIMIT combination of index optimization, shape as:

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

This statement can not get the expected result if you use the indexing method in the second example (using index on [sort] only, using where; WHERE WHERE there is; using filesort) for the reason that columnX Corresponding to more.

How to optimize this statement? I did not think of a good way for a while now. Seeing that the internet is cheaper, I split it up with UNION and 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, this method has been proved that this method does not work at all, but lower efficiency, the test for the most part of the mandatory use of the specified index is better

4, Do not apply expressions (functions) on fields of WHERE and ORDER BY, for example:

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, we may add one such index (x, y, uid). But in fact the better effect is (uid, x, y). This is caused by the mechanism that MySQL handles sorting.

The above example you applied in the actual project, do not forget to add the index, use EXPLAIN to see the effect.