Optimizer news: @@optimizer_switch syntax changes and backport

In short, the news are:

  • @@optimizer_switch support was backported into MySQL 5.1
  • The switch syntax was changed from ‘no_optimization_name’ to ‘optimization_name=on|off|default’.
  • Added switches for index_merge, index_merge_intersection, index_merge_union, and index_merge_sort_union optimizations.

The changes will be available in next releases, that is, MySQL 5.1.34 and 6.0.11.
Now with more details:

New switch names

Until now, the syntax mimicked an enum column or the @@sql_mode variable. One could set the value of @@optimizer_switch to a set of keywords, e.g.

SET @@optimizer_switch='no_semijoin,no_materialization';

Presence of a no_xxx keyword meant that its optimization was disabled, its absence meant it was disabled.

As of the next MySQL 5.1/6.0, @@optimizer_switch value is a set of on/off flags:

mysql> SELECT @@optimizer_switch;
+-------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                        |
+-------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
+-------------------------------------------------------------------------------------------+

the SET statement accepts a list of commands:

SET [GLOBAL|SESSION] optimizer_switch='command,command,...'

where each command is one of


default reset all optimization settings to default
optimization_name=off disable the optimization
optimization_name=on enable the optimization
optimization_name=default set the optimization to its default state

The order of the commands does not matter (’default’ will be executed first if present), setting the same flag twice within one SET command is not allowed. Flags that are not mentioned keep their current values:

mysql> SELECT @@optimizer_switch;
+-------------------------------------------------------------------------------------------+
| @@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)

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;
+---------------------------------------------------------------------------------------------+
| @@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)

The advantages of the new way over the old one are that

  1. It is now possible to turn certain optimization on/off with a single statement (SET optimizer_switch=’malfunctioning_optimization=off’) which will not depend on what other optimizer flags exist and what their values are.
  2. One can easily see what optimizer switches are available in the current server.
  3. In contrast to the grand solution of WL#4046, I could code and push this within a reasonable amount of time.

In addition, the mysqld binary got a –optimizer-switch parameter which allows to set optimizer_switch at server startup or in my.cnf file.

The backport

This is actually the reason for making all these changes. It turns out that the optimizer can make a wrong choice when considering whether to use index_merge optimization. This can happen for both valid (unknown data correlations) and not-so-valid (mismatch between cost model and the reality) reasons. A fix for either of these problems would be too intrusive to put into the GA version (betas and major releases were invented for a reason), and we also just do not have it yet. So, we’ve decided to provide at least some resolution for those for whom index_merge made things worse and introduced the following switches


@@optimizer_switch flags in MySQL 5.1
index_merge turns on/off the all index_merge optimizations
index_merge_union turn on/off individual index_merge algorithms (names as in the documentation)
index_merge_sort_union
index_merge_intersection


MySQL 6.0 has the above switches and also subquery optimization switches:

@@optimizer_switch flags in MySQL 6.0
semijoin turns on/off the all semi-join optimizations
materialization turns on/off materialization (including semi-join materialization)
loosescan turns on/off semi-join LooseScan strategy (not to be confused with GROUP BY’s LooseScan)
firstmatch turns on/off semi-join FirstMatch strategy

All future optimizations will be switchable as well. We’ve learned the lesson.

Posted in Uncategorized on April 14th, 2009 by spetrunia | |

2 Responses to ' Optimizer news: @@optimizer_switch syntax changes and backport '

Subscribe to comments with RSS or TrackBack to ' Optimizer news: @@optimizer_switch syntax changes and backport '.


  1. on April 17th, 2009 at 8:58 pm

    […] Interestingly, the feature preview is based on MySQL 5.1, which has recently seemed to be getting some significant changes even though it’s a GA release. Does this signal a change to MySQL’s release cycle, which has sometimes been characterized as too long? More good news? […]

  2. Alex Gordon said,

    on April 3rd, 2010 at 3:17 pm

    реально улет!ждем с нетерпением релиза и будем зажигать!!!!!!…


    The changes will be available in next releases, that is, MySQL 5.1.34 and 6.0.11…..

Leave a reply

You must be logged in to post a comment.