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

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

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

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