My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Add to Memories Share Next Entry
Working with comma separated list MySQL options
swanhart
Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on



As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:
mysql> select replace(@@optimizer_switch, ',','\n')\G
*************************** 1. row ***************************
replace(@@optimizer_switch, ',','\n'): index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
subquery_materialization_cost_based=on
use_index_extensions=on
1 row in set (0.00 sec)


This way you can use the \P mysql monitor option to search for key/value pairs:
mysql> \P grep mrr
PAGER set to 'grep mrr'
mysql> select replace(@@optimizer_switch, ',','\n')\G
mrr=on
mrr_cost_based=on
1 row in set (0.00 sec)

You are viewing swanhart