Deep Dive into MySQL optimizer_switch: Controlling Query Optimizer Strategies

MySQL query optimizer selects the most efficient execution plan for SQL statements. The optimizer_switch system variable lets users enable or disable individual optimizer strategies at runtime.

Concept of optimizer_switch

optimizer_switch

is a comma‑separated list of flags, each controlling a specific optimizer behavior. Flags accept on or off. The variable has both global and session scope; the global default can be set at server startup.

Viewing the current flag set

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,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on
1 row in set (0.00 sec)

Modifying optimizer_switch

Use a

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

statement. Each command follows the format shown in the image below; order is irrelevant, but the default command (if present) is applied first. Setting a flag to default restores its built‑in default (on or off). A flag cannot appear more than once.

Main optimizer flags

index_merge – Allows MySQL to merge multiple indexes to satisfy a query, improving performance for complex conditions.

index_condition_pushdown (ICP) – Pushes WHERE‑clause predicates to the storage engine, reducing rows returned to the optimizer.

materialization – Controls whether subquery results are materialized in a temporary table; materialization avoids repeated computation but may increase memory usage.

semijoin and loosescan – Enable semi‑join optimization for EXISTS/IN queries ( semijoin) and allow more efficient scans in certain cases ( loosescan).

derived_merge – Attempts to merge derived tables (temporary tables from subqueries) into the outer query, reducing query complexity.

exists_to_in – Converts EXISTS clauses to IN clauses when the optimizer determines it yields a better plan.

mrr (Multi‑Range Read) – Enables a technique that reads index ranges more efficiently, often lowering disk I/O for range queries and joins.

mrr_cost_based – Lets the optimizer decide based on cost whether to use MRR; if the cost model favors MRR, it is used, otherwise the traditional method is applied.

block_nested_loop – Enables Block Nested Loop Join, which processes join rows in blocks to reduce I/O.

batched_key_access – Enables Batched Key Access (BKA) for certain joins, decreasing the number of index lookups.

use_index_extensions – Allows the optimizer to use extra information stored in an index to filter rows without a table lookup.

condition_fanout_filter – Activates the Condition Fanout Filter (CFF), reducing unnecessary row scans when a column has many possible values.

use_invisible_indexes – Makes the optimizer consider indexes marked invisible, useful for testing or maintenance.

skip_scan – Permits the optimizer to use skip‑scan, jumping over index entries to locate matching rows faster.

duplicateweedout – Removes duplicate rows produced by certain join operations when set to on, improving result accuracy.

subquery_materialization_cost_based – Lets the optimizer decide based on cost whether to materialize a subquery, balancing performance and memory usage.

Using optimizer_switch

View the current settings: SHOW VARIABLES LIKE 'optimizer_switch'; Enable a specific flag, for example ICP:

SET optimizer_switch='index_condition_pushdown=on';

Apply the change globally:

SET GLOBAL optimizer_switch='index_condition_pushdown=on';