optimizer_switch

optimizer_switch is a server variable that one can use to enable/disable specific optimizations.

Syntax

To set or unset the various optimizations, use the following syntax:

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

The cmd takes the following format:

SyntaxDescription
defaultReset all optimizations to their default values.
optimization_name=defaultSet the specified optimization to its default value.
optimization_name=onEnable the specified optimization.
optimization_name=offDisable the specified optimization.

There is no need to list all flags - only those that are specified in the command will be affected.

Available Flags

Below is a list of all optimizer_switch flags available in MariaDB:

Flag and MariaDB defaultSupported in MariaDB sinceSupported in MySQL since
condition_pushdown_for_derived=onMariaDB 10.2.2-
condition_pushdown_from_having=onMariaDB 10.4.3-
condition_pushdown_for_subquery=onMariaDB 10.4.0-
derived_merge=onMariaDB 5.3MySQL 5.7
derived_with_keys=onMariaDB 5.3-
defaultMariaDB 5.1MySQL 5.1
engine_condition_pushdown=offMariaDB 5.5 (deprecated in 10.1)MySQL 5.5
exists_to_in=onMariaDB 10.0-
extended_keys=onMariaDB 5.5.21-
firstmatch=onMariaDB 5.3MySQL 5.6
index_condition_pushdown=onMariaDB 5.3MySQL 5.6
index_merge=onMariaDB 5.1MySQL 5.1
index_merge_intersection=onMariaDB 5.1MySQL 5.1
index_merge_sort_intersection=offMariaDB 5.3-
index_merge_sort_union=onMariaDB 5.1MySQL 5.1
index_merge_union=on#MariaDB 5.1MySQL 5.1
in_to_exists=onMariaDB 5.3-
join_cache_bka=onMariaDB 5.3-
join_cache_hashed=onMariaDB 5.3-
join_cache_incremental=onMariaDB 5.3-
loosescan=onMariaDB 5.3MySQL 5.6
materialization=on (semi-join, non-semi-join)MariaDB 5.3MySQL 5.6
mrr=offMariaDB 5.3MySQL 5.6
mrr_cost_based=offMariaDB 5.3MySQL 5.6
mrr_sort_keys=offMariaDB 5.3-
optimize_join_buffer_size=onMariaDB 5.3, Defaults to ON from MariaDB 10.4.3-
orderby_uses_equalities=onMariaDB 10.1.15-
outer_join_with_cache=onMariaDB 5.3-
partial_match_rowid_merge=onMariaDB 5.3-
partial_match_table_scan=onMariaDB 5.3-
row_id_filter=onMariaDB 10.4.3-
semijoin=onMariaDB 5.3MySQL 5.6
semijoin_with_cache=onMariaDB 5.3-
split_materialized=on[1]MariaDB 10.3.4-
subquery_cache=onMariaDB 5.3-
table_elimination=onMariaDB 5.1-
  1. replaced split_grouping_derived, introduced in MariaDB 10.3.1

Defaults

From versionDefault optimizer_switch setting
MariaDB 10.4.3index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=on, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on, condition_pushdown_for_subquery=on, rowid_filter=on,condition_pushdown_from_having=on
MariaDB 10.4.0index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on, condition_pushdown_for_subquery=on
MariaDB 10.3.4index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on
MariaDB 10.2.2index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on
MariaDB 10.1.15index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=off
MariaDB 10.0.12index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on
MariaDB 10.0.9index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=off
MariaDB 10.0.2index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=off, exists_to_in=off
MariaDB 5.5.21index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=off
MariaDB 5.5.20index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
MariaDB 5.3.3index_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=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
MariaDB 5.3.0index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, index_condition_pushdown=off, derived_merge=off, derived_with_keys=off, firstmatch=off, loosescan=off, materialization=off, in_to_exists=on, semijoin=off, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=off, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, 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=off, table_elimination=on
< MariaDB 5.3.0index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on

Comments

Comments loading...