8.9 controlling the Query Optimizer

8.9 controlling the Query Optimizer

8.9.1 Controlling Query Plan evaluation
8.9.2 Controlling switchable Optimizations
8.9.3 Index Hints

MySQL provides optimal control through system variables, impact query plan evaluation, Control optimizer and index hints.

8.9.1 Controlling query Plan evaluation control query Planning evaluation

The task of the query optimizer is to find an appropriate execution plan to execute an SQL query,

Because good execution plans and poor execution plan performance are magnitude differences,

Many of the query plans include MySQL, including or less detailed search for an optimal solution in more likely query evaluation scenarios.

For a join query, for an associated query, for small data tables (usually less than 7 to 10) This is not a problem. However, when a larger query is committed, the time spent in query optimization can easily become a major bottleneck in server performance.

A more complex approach is used to query the optimizer to enable user control to search for the best query plan.

The general approach is to spend less time compiling a query, in other words, because the optimizer skips some plans,

It may be missed to find an optimal plan.

The Optimizer_prune_level variable tells the optimizer to skip the evaluation of certain schedules based on the accessed records.

Our experience shows that this type of based guesses rarely bugs the optimal execution plan,

and may play to reduce query compile time. That's why the default is (Optimizer_prune_level = 1),

However, if you are sure that the optimizer missed a better plan, this option can be turned off (optimizer_prune_level=0)

This can take a long time to compile the query.