- 7.2 Optimizing SELECT and Other Statements
- 7.2.1 Optimizing Queries with EXPLAIN
- 7.2.2 Estimating Query Performance
- 7.2.3 Speed of SELECT Queries
- 7.2.4 WHERE Clause Optimization
- 7.2.5 Range Optimization
- 7.2.6 Index Merge Optimization
- 7.2.7 Condition Pushdown Optimization
- 7.2.8 IS NULL Optimization
- 7.2.9 LEFT JOIN and RIGHT JOIN Optimization
- 7.2.10 Nested-Loop Join Algorithms
- 7.2.11 Nested Join Optimization
- 7.2.12 Outer Join Simplification
- 7.2.13 ORDER BY Optimization
- 7.2.14 GROUP BY Optimization
- 7.2.15 DISTINCT Optimization
- 7.2.16 Optimizing IN/=ANY Subqueries
- 7.2.17 LIMIT Optimization
- 7.2.18 Using optimizer_switch to Control the Optimizer
- 7.2.19 How to Avoid Table Scans
- 7.2.20 INFORMATION_SCHEMA Optimization
- 7.2.21 Speed of INSERT Statements
- 7.2.22 Speed of UPDATE Statements
- 7.2.23 Speed of DELETE Statements
- 7.2.24 Other Optimization Tips
The optimizer_switch system
variable enables control over optimizer behavior.
As of MySQL 5.1.34, the value of the
optimizer_switch system
variable is a set of flags, each of which has a value of
on or off to indicate
whether the corresponding optimizer behavior is enabled or
disabled. This variable has global and session values and be
changed at runtime. The global default can be set at server
startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on
To change the value of
optimizer_switch, assign a
value consisting of a comma-separated list of one or more
commands:
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
Each command value should have one of
the forms shown in the following table.
| Command Syntax | Meaning |
default |
Reset every optimization to its default value |
|
Set the named optimization to its default value |
|
Disable the named optimization |
|
Enable the named optimization |
The order of the commands in the value does not matter, although
default is executed first if present.
Specifying any given opt_name more
than once in the value is not allowed and causes an error. Any
errors in the value cause the entire statement to fail with an
error and the current value of
optimizer_switch remains
unchanged.
The following table lists the allowable
opt_name flag names.
| Flag Name | Meaning |
index_merge |
Controls all Index Merge optimizations |
index_merge_union |
Controls the Index Merge Union Access optimization |
index_merge_sort_union |
Controls the Index Merge Sort-Union Access optimization |
index_merge_intersection |
Controls the Index Merge Intersection Access optimization |
For information about Index Merge, see Section 7.2.6, “Index Merge Optimization”.
When you assign a value to
optimizer_switch, flags that
are not mentioned keep their current values. This makes it
possible to enable or disable specific optimizer behaviors in a
single statement without affecting other behaviors. The
statement does not depend on what other optimizer flags exist
and what their values are. Suppose that all Index Merge
optimizations are enabled:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on
1 row in set (0.00 sec)
If the server is using the Index Merge Union or Index Merge Sort-Union access methods for certain queries and you want to check whether the optimizer will perform better without them, set the variable value like this:
mysql>SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';Query OK, 0 rows affected (0.00 sec) mysql>SELECT @@optimizer_switch\G*************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=off, index_merge_sort_union=off, index_merge_intersection=on 1 row in set (0.00 sec)
User Comments
Add your own comment.