High-performance MySQL (6) How to optimize a specific type of query

As long as the description of several day-to-day use of the query can be optimized place, only special circumstances can be used, do not expect any circumstances are set up, optimization is a long process of practice!

Optimization of maximum and minimum values

For min () and Max () queries, MySQL optimization is not very good, for example:

Select min (actor_id) from actor where first_name = ' Jane ';

Because there is no index on the first_name, this will scan the entire table. If MySQL can press the primary key to scan, then the first to meet the conditions of the record is to find the smallest point, because the primary key is strictly in order of size, one can try the method is not min (), using limit:

Select actor_id from actor use index (primary) where first_name = ' Jane ' limit 1;

But the meaning of this SQL is not obvious.

Second, query and update on the same table

MySQL does not allow simultaneous querying and updating of the same table, for example

Update TAB1 as Outer_tab

Set cnt = (

Select COUNT (*) from TAB1 as Inner_tab

where Inner_tab.type = Outer_tab.type

);

You can use the form of a build table to bypass the above restrictions, which are completed before the UPDATE statement is opened.

Update Tab1

INNER JOIN (

Select Type,count (*) as CNT from TAB1 Group by type)

As Der using (type)

Set tab1.cnt = der.cnt;

Iii. using optimizer tips (hint)

Here's a few important points to illustrate

1, delayed

This hint is valid for insert and replace. MySQL returns the statement that uses the prompt to the client immediately, puts the inserted data in the buffer, and then writes the data in bulk when the table is idle. It is especially suitable for applications that need to write large amounts of data but the client does not need to wait for statements to complete I/O.

2, Straight_join

This hint can be placed after the SELECT keyword in the SELECT statement, or between the names of any two associated tables. The first usage is to have all the tables in the query be associated in the order in which they appear in the statement. The second rule is to set the association order of the two tables before and after. When you can determine the best association order, you can use, but when the version upgrade to re-examine the class query.

3, Sql_small_result and Sql_big_result

This prompt is valid only for SELECT statements. It tells the optimizer how to use temporary tables and sorting for group by or distinct queries.

4, Sql_buffer_result

This hint tells the optimizer to put the query results in a temporary table and then release the table lock as quickly as possible.

5, Sql_cache and Sql_no_cache

This hint tells MySQL whether the result set should be cached in the query cache.

6, for UPDATE and LOCK in SHARE MODE

Control row lock, only for the InnoDB engine, this will be introduced later.

7, use index, IGNORE Index and FORCE index

Use or do not use indexes to query records.