Attempt to split @@optimizer_switch

Three years ago MySQL has got @@optimizer_switch variable. It was introduced in MySQL 5.1.34, that is, we needed it so much that we’ve added it into the stable release.

In a nutshell,@@optimizer_switch held a comma-separated list of optimizer controls:

mysq> > 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)

One could set all settings at once:

mysql> set optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=off';
Query OK, 0 rows affected (0.01 sec)

or set individual flags:

mysql> set optimizer_switch='index_merge_sort_union=off';
Query OK, 0 rows affected (0.01 sec)

The reasons for putting all optimizer parameters into one variable were:

• make them distinct from all other settings
• make it possible to run “SET optimizer_switch=default” and reset the optimizer to its default settings (which are not necessarily all “on”)

@@optimizer_switch solution allowed all that, and was very useful in optimizer development and troubleshooting. However, it is becoming a victim of its own success. In current development version of MariaDB @@optimizer_switch has 26 flags, and we’re thinking of adding at least two more before MariaDB 5.3 release. It now looks like this:

MariaDB [test]&> select @@optimizer_switch;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,
in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,
subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,
semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,
optimize_join_buffer_size=on,table_elimination=on
1 row in set (0.00 sec)

It is rather difficult to check the value of some particular flag. Also, there is no easy way to get all settings for subquery optimization flags (other than knowing them by heart and checking each of them).

We at MariaDB are having a discussion about switching from single @@optimizer_switch variable to a set of variables like this:

optimizer.index_merge=on
optimizer.index_merge.union=on
optimizer.index_merge.sort_union=on
optimizer.index_merge.intersection=on
optimizer.index_merge.sort_intersection=off
optimizer.index_condition_pushdown=on
optimizer.join_cache.bka=on
optimizer.join_cache.hashed=on
optimizer.join_cache.incremental=on
optimizer.join_cache.optimize_buffer_size=on
optimizer.join_cache.outer_join=off
optimizer.join_cache.semijoin=off
optimizer.mrr=on
optimizer.mrr.cost_based=off
optimizer.mrr.sort_keys=on
optimizer.semijoin=on
optimizer.semijoin.firstmatch=on
optimizer.semijoin.loosescan=on
optimizer.subquery.cache=on
optimizer.subquery.in_to_exists=on
optimizer.subquery.materialization=off
optimizer.subquery.partial_match_rowid_merge=on
optimizer.subquery.partial_match_table_scan=on
optimizer.table_elimination=on

There are various opinions about how we could make the switch while remaining compatible with the current approach, whether we should switch at all/the optimizer_switch variable/all variables to dotted.name.notation, etc.
If you have an opinion, now it is a good time to voice it at maria-developers@lists.launchpad.net.