--optimizer-switch

This page is part of MariaDB's Documentation.

The parent of this page is: mariadbd & mysqld for MariaDB Enterprise Server

Topics on this page:

• Overview

• USAGE

• DETAILS

  • SYNONYMS

  • SCHEMA

  • PARAMETERS

  • SKYSQL

  • PRIVILEGES

• EXAMPLES

• ERROR HANDLING

• FEATURE INTERACTION

• RESPONSES

• DIAGNOSIS

• ISO 9075:2016

• CHANGE HISTORY

• RELATED TOPICS

• EXTERNAL REFERENCES

Overview

This is a command-line option for the mariadbd & mysqld commands (arg: Required).

In 11.4 ES, 10.6 ES:

Fine-tune the optimizer behavior. Takes a comma-separated list of option=value pairs, where value is on, off, or default, and options are: index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, index_merge_sort_intersection, engine_condition_pushdown, index_condition_pushdown, derived_merge, derived_with_keys, firstmatch, loosescan, materialization, in_to_exists, semijoin, partial_match_rowid_merge, partial_match_table_scan, subquery_cache, mrr, mrr_cost_based, mrr_sort_keys, outer_join_with_cache, semijoin_with_cache, join_cache_incremental, join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, condition_pushdown_for_subquery, rowid_filter, condition_pushdown_from_having, not_null_range_scan, hash_join_cardinality, sargable_casefold

In 10.6 CS:

Fine-tune the optimizer behavior. Takes a comma-separated list of option=value pairs, where value is on, off, or default, and options are: index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, index_merge_sort_intersection, engine_condition_pushdown, index_condition_pushdown, derived_merge, derived_with_keys, firstmatch, loosescan, materialization, in_to_exists, semijoin, partial_match_rowid_merge, partial_match_table_scan, subquery_cache, mrr, mrr_cost_based, mrr_sort_keys, outer_join_with_cache, semijoin_with_cache, join_cache_incremental, join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, condition_pushdown_for_subquery, rowid_filter, condition_pushdown_from_having, not_null_range_scan, hash_join_cardinality

In 10.5 ES, 10.5 CS:

Fine-tune the optimizer behavior. Takes a comma-separated list of option=value pairs, where value is on, off, or default, and options are: index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, index_merge_sort_intersection, engine_condition_pushdown, index_condition_pushdown, derived_merge, derived_with_keys, firstmatch, loosescan, materialization, in_to_exists, semijoin, partial_match_rowid_merge, partial_match_table_scan, subquery_cache, mrr, mrr_cost_based, mrr_sort_keys, outer_join_with_cache, semijoin_with_cache, join_cache_incremental, join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, condition_pushdown_for_subquery, rowid_filter, condition_pushdown_from_having, not_null_range_scan

In 10.4 ES, 10.4 CS:

Fine-tune the optimizer behavior. Takes a comma-separated list of option=value pairs, where value is on, off, or default, and options are: index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, index_merge_sort_intersection, engine_condition_pushdown, index_condition_pushdown, derived_merge, derived_with_keys, firstmatch, loosescan, materialization, in_to_exists, semijoin, partial_match_rowid_merge, partial_match_table_scan, subquery_cache, mrr, mrr_cost_based, mrr_sort_keys, outer_join_with_cache, semijoin_with_cache, join_cache_incremental, join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, condition_pushdown_for_subquery, rowid_filter, condition_pushdown_from_having

In 10.3 ES, 10.3 CS:

Fine-tune the optimizer behavior. Takes a comma-separated list of option=value pairs, where value is on, off, or default, and options are: index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, index_merge_sort_intersection, engine_condition_pushdown, index_condition_pushdown, derived_merge, derived_with_keys, firstmatch, loosescan, materialization, in_to_exists, semijoin, partial_match_rowid_merge, partial_match_table_scan, subquery_cache, mrr, mrr_cost_based, mrr_sort_keys, outer_join_with_cache, semijoin_with_cache, join_cache_incremental, join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized

In 10.2 ES, 10.2 CS:

Fine-tune the optimizer behavior. Takes a comma-separated list of option=value pairs, where value is on, off, or default, and options are: index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection, index_merge_sort_intersection, engine_condition_pushdown, index_condition_pushdown, derived_merge, derived_with_keys, firstmatch, loosescan, materialization, in_to_exists, semijoin, partial_match_rowid_merge, partial_match_table_scan, subquery_cache, mrr, mrr_cost_based, mrr_sort_keys, outer_join_with_cache, semijoin_with_cache, join_cache_incremental, join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived

See also: mariadbd & mysqld for MariaDB Enterprise Server 11.4, in 10.6 ES, in 10.5 ES, in 10.4 ES, in 10.3 ES, in 10.2 ES, in 10.6 CS, in 10.5 CS, in 10.4 CS, in 10.3 CS, and in 10.2 CS