# optimizer\_switch [optimizer\_switch](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#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: ```sql SET [GLOBAL|SESSION] optimizer_switch='cmd[,cmd]...'; ``` The *cmd* takes the following format: | Syntax | Description | | -------------------------- | ---------------------------------------------------- | | default | Reset all optimizations to their default values. | | optimization\_name=default | Set the specified optimization to its default value. | | optimization\_name=on | Enable the specified optimization. | | optimization\_name=off | Disable 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 default | Supported in MariaDB since | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | [condition\_pushdown\_for\_derived=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/condition-pushdown-into-derived-table-optimization) | [MariaDB 10.2.2](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.2/10.2.2) | | [condition\_pushdown\_for\_subquery=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/condition-pushdown-into-in-subqueries) | [MariaDB 10.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/what-is-mariadb-104) | | [condition\_pushdown\_from\_having=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/condition-pushdown-into-derived-table-optimization) | [MariaDB 10.4.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/10.4.3) | | [cset\_narrowing=on/off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/charset-narrowing-optimization) | [MariaDB 10.6.16](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.6/10.6.16), [MariaDB 10.11.6](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.11/10.11.6), [MariaDB 11.0.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.0/11.0.4), [MariaDB 11.1.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.1/11.1.3) and [MariaDB 11.2.2](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.2/11.2.2) | | [derived\_merge=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/derived-table-merge-optimization) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [derived\_with\_keys](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/derived-table-with-key-optimization)=on | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | default | [MariaDB 5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.1/changes-improvements-in-mariadb-5-1) | | duplicateweedout=on | [MariaDB 12.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/12.0/what-is-mariadb-120) | | engine\_condition\_pushdown=off | [MariaDB 5.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.5/changes-improvements-in-mariadb-5-5) (deprecated in [MariaDB 10.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.1/changes-improvements-in-mariadb-10-1), removed in [MariaDB 11.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/what-is-mariadb-113)) | | [exists\_to\_in=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/exists-to-in-optimization) | [MariaDB 10.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.0/changes-improvements-in-mariadb-10-0) | | [extended\_keys=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/extended-keys) | [MariaDB 5.5.21](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.5/5.5.21) | | [firstmatch=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/firstmatch-strategy) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [index\_condition\_pushdown=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/index-condition-pushdown) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [hash\_join\_cardinality=off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/hash_join_cardinality-optimizer_switch-flag) | [MariaDB 10.6.13](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.6/10.6.13) ([MDEV-30812](https://jira.mariadb.org/browse/MDEV-30812)) | | index\_merge=on | [MariaDB 5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.1/changes-improvements-in-mariadb-5-1) | | index\_merge\_intersection=on | [MariaDB 5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.1/changes-improvements-in-mariadb-5-1) | | [index\_merge\_sort\_intersection=off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/index_merge-sort_intersection) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | index\_merge\_sort\_union=on | [MariaDB 5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.1/changes-improvements-in-mariadb-5-1) | | index\_merge\_union=on# | [MariaDB 5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.1/changes-improvements-in-mariadb-5-1) | | [in\_to\_exists=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/subquery-optimizations/non-semi-join-subquery-optimizations#the-in-to-exists-transformation) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [join\_cache\_bka=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [join\_cache\_hashed=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [join\_cache\_incremental=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [loosescan=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/loosescan-strategy) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | materialization=on ([semi-join](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/semi-join-materialization-strategy), [non-semi-join](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/subquery-optimizations/non-semi-join-subquery-optimizations#materialization-for-non-correlated-in-subqueries)) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [mrr=off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/mariadb-internal-optimizations/multi-range-read-optimization) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [mrr\_cost\_based=off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/mariadb-internal-optimizations/multi-range-read-optimization) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [mrr\_sort\_keys=off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/mariadb-internal-optimizations/multi-range-read-optimization) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [not\_null\_range\_scan=off](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/not_null_range_scan-optimization) | [MariaDB 10.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/what-is-mariadb-105) | | [optimize\_join\_buffer\_size=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [orderby\_uses\_equalities=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/improvements-to-order-by) | [MariaDB 10.1.15](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.1/10.1.15) | | [outer\_join\_with\_cache=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [partial\_match\_rowid\_merge=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [partial\_match\_table\_scan=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [reorder\_outer\_joins](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/reorder_outer_joins) | [MariaDB 12.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/12.3/mariadb-12.3-changes-and-improvements) | | [rowid\_filter=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/rowid-filtering-optimization) | [MariaDB 10.4.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/10.4.3) | | [sargable\_casefold=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/sargable-upper) | [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0) | | [semijoin=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/semi-join-subquery-optimizations) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [semijoin\_with\_cache=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [split\_materialized=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/lateral-derived-optimization) | [MariaDB 10.3.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.3/10.3.4) | | [subquery\_cache=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/subquery-cache) | [MariaDB 5.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3) | | [table\_elimination=on](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/table-elimination/table-elimination-user-interface) | [MariaDB 5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/5.1/changes-improvements-in-mariadb-5-1) | ## Defaults | From version | Default optimizer\_switch setting | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | [MariaDB 12.0.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/12.0/12.0.1) | index\_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, **duplicateweedout=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, not\_null\_range\_scan=off, hash\_join\_cardinality=on, cset\_narrowing=on, sargable\_casefold=on | | [MariaDB 11.7.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.7/11.7.0) | index\_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=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, not\_null\_range\_scan=off, hash\_join\_cardinality=on, **cset\_narrowing=on**, sargable\_casefold=on | | [MariaDB 11.3.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.1) | index\_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=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, not\_null\_range\_scan=off, hash\_join\_cardinality=on, cset\_narrowing=off, **sargable\_casefold=on** | | [MariaDB 10.6.16](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.6/10.6.16), [MariaDB 10.11.6](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.11/10.11.6), [MariaDB 11.0.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.0/11.0.4), [MariaDB 11.1.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.1/11.1.3) and [MariaDB 11.2.2](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.2/11.2.2) | index\_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=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, not\_null\_range\_scan=off, hash\_join\_cardinality=on, **cset\_narrowing=off** | | [MariaDB 11.0.2](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.0/11.0.2) | index\_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, not\_null\_range\_scan=off, **hash\_join\_cardinality=on** | | [MariaDB 10.6.13](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.6/10.6.13), [MariaDB 10.11.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.11/10.11.3) | index\_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, not\_null\_range\_scan=off, **hash\_join\_cardinality=off** | ## See Also * [Quickly finding optimizer\_switch values that are on or off](https://mariadb.com/docs/server/reference/system-tables/sys-schema/sys-schema-stored-procedures/optimizer_switch-helper-functions) * [The optimizer converts certain big IN predicates into IN subqueries](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/conversion-of-big-in-predicates-into-subqueries) * [optimizer\_adjust\_secondary\_key\_cost](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizer_adjust_secondary_key_costs) * [Optimizer hints in SELECT](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimizer-hints) *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}