How to optimize MySQL statements through Indexes

BY using indexes, You can optimize MySQL statements containing order by statements. The following describes how to optimize MySQL statements in detail, hoping to help you learn about MySQL statement optimization.

Several criteria for creating an index:

1. Reasonable indexing can accelerate data reading efficiency. improper indexing slows down the response speed of the database.

2. The more indexes, the slower the data update speed.

3. Try to use the index when using MyIsam as the engine because MySQL stores the index with a BTree) instead of InnoDB. However, MyISAM does not support Transcation.

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

5. Use EXPLAIN to analyze the performance of your SQL statements.

An easy mistake:

Do not place an index on the selected column. This is meaningless. The index should be properly placed on the statement selected by the condition, such as where and order.

MySQL statement optimization example:

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

In the preceding statement, it is meaningless to place an index on id/title/content without any Optimization for this statement. However, if you place an index on the foreign key cat_id, the function will be quite significant.

MySQL statement Optimization for several commonly used order by statements:

1. order by + LIMIT combination index optimization. If an SQL statement is like:

SELECT [column1], [column2],... FROM [TABLE] order by [sort] LIMIT [offset], [LIMIT];

This SQL statement is easy to optimize. You can create an index on the [sort] field.

2. the index optimization of the where + order by + LIMIT combination, such:

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

If you still use the index creation method in the first example, you can use the index, but the efficiency is not high. A more efficient method is to create a composite index (columnX, sort)

3. the index optimization of the where + IN + order by + LIMIT combination is shown IN the following figure:

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

If you use the index creation method in the second example, this statement will not achieve the expected effect only in [sort], WHERE is using where; using filesort ), the reason is that columnX values correspond to multiple values.

How can this statement be optimized? I have not thought of any good method for the moment. If I see a cheap solution on the internet, I will split the statement with UNION and then create 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 verified that this method does not work at all, and the efficiency is lower. During the test, it is better for most applications to forcibly specify to use the Sorting index.

4. Do not apply expressions (functions) on the WHERE and order by columns, for example:

SELECT * FROM [table] order by year (date) LIMIT 0, 30;

5. WHERE + order by multiple columns + LIMIT, for example

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

For this statement, you may add an index (x, y, uid ). But in fact, the better effect is (uid, x, y ). This is caused by the sorting mechanism of MySQL.

When using the above example in a project, do not forget to use the EXPLAIN command to check the effect after adding an index.

How to use mysql UPDATE statements

How to Set auto-increment fields in MySQL

Default length of common MySql Fields

General INSERT usage in MySQL

How to use mysql UPDATE statements