How can I change the values of optimizer_switch variables for MySQL?

Introduction

In the event you would want to change the value a specific optimizer_switch variable is set to for MySQL, it would be useful to refer to the MySQL documentation on Optimization.

Please note that optimizations to the database engine are best decided by and performed by a qualified system administrator.

For reference, you can see the list of available variables by running the following query:

mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
1 row in set (0.02 sec)

Changing variable values

At runtime, you can change the value a specific variable is set to using the following format:

mysql> set optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

Or you could change the value at startup using the MySQL configuration file and the following format:

[mysqld]
optimizer_switch=derived_merge=off

For further information, reference the MySQL documentation:

https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

https://dev.mysql.com/doc/refman/8.0/en/engine-condition-pushdown-optimization.html