Hello, Please find below descriptions of problems with current @@optimizer_switch variable, as well as a proposal on how to fix it. <contents> Problems with @@optimizer_switch Proposed solution Details about hooking this into parser Alternate approach1: grouping </contents> Problems with @@optimizer_switch -------------------------------- @@optimizer_switch variable use has outgrown its syntax. There are too many settings to fit on the single line: MariaDB [(none)]> select @@optimizer_switch; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@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,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) The line is now 510 characters long. It's hard to find the flag you're looking for, and one needs to write regular expressions if they want to check the value of a particular flag in some program. There is no easy way to get settings for a subset of flags (which means we've banned "select @@optimizer_switch" from almost all .test files, because one had to change .result file all the time) The extra-long line also gets into "SHOW VARIABLES" output, and makes it hard-to-read, too. The situation is going to get worse, as we've already committed to adding even to add even more switches (Igor's patch adds some, then we've decided that two semi-join optimizations that do not have their switches, should have them) The benefits of having all settings grouped together are: - It was easy to implement - One can easily see all optimizer switches without having to fish them out of the list of all server variables. - One can reset all optimizer settings with a single statement: SET @@optimizer_switch=default Proposed solution ----------------- Break optimizer_switch into multiple variables, with names having the dots in them. That is, current @@optimizer_switch flags will change into this set of variables: 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 The dots are there so that - the structuring is more apparent - it is possible to reset groups of variables to their defaults. Current idea is to use syntax like this: SET optimizer.semijoin.*= default; - in the future we may also allow to select and set groups of settings. Details about hooking this into parser ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Currently, dotted syntax is allowed to some extent: we allow - GLOBAL/SESSION/DEFAULT prefix for any variable - key_cache_name prefix for multiple named key caches. There is one key cache named default, it is not possible to make a key cache named "session". To make variable lookup rules easy, we will diallow creation/use of key caches named 'optimizer'. Overall, system variables can be encountered - as part of @@variable syntax when their value is selected - as part of SET statement. in both cases, bison can easily see it's parsing a system variable name, so I was able to add support for dotted variable syntax into the parser without introducing any extra conflicts. Alternate approach1: grouping ----------------------------- (Suggested by Igor (or was it Timour?)) Replace one @@optimizer_switch variable with multiple: @@optimizer_switch_semijoin @@optimizer_switch_mrr .. and so forth. The advantage of this approach is that it's easy and we don't need to change the parser. The disadavantage is that it may turn out that groups of flags may be still too big, and so either look ugly or require further splits. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog