All Products
Search

This Product

    ApsaraDB RDS:Modify the loose_optimizer_switch parameter

    Document Center

    ApsaraDB RDS:Modify the loose_optimizer_switch parameter

    Last Updated:Feb 28, 2026

    The loose_optimizer_switch parameter controls MySQL query optimizer behaviors in ApsaraDB RDS for MySQL. Modify individual flags in this parameter to enable or disable specific optimizer strategies and tune query performance for your workload.

    Alibaba Cloud adapts the open source MySQL optimizer_switch as the loose_optimizer_switch parameter. The loose_ prefix allows the parameter to be safely set even when the underlying MySQL version does not support a particular flag -- unrecognized flags are silently ignored rather than causing an error.

    Before you begin

    • Understand each flag's behavior before changing it. Disabling a core optimization can degrade query performance. For flag definitions, see Switchable optimizations in the MySQL documentation.

    • Change one flag at a time and observe query performance before making additional changes. Use EXPLAIN to verify how each change affects query execution plans.

    • Base changes on your specific workload characteristics: data volume, query patterns, and instance type.

    • Flags vary by MySQL version. Check the tables below to confirm flag availability for your RDS instance's MySQL version and minor engine version.

    Flags by MySQL version

    Flags in loose_optimizer_switch are backward-compatible. A later version supports all flags from earlier versions.

    MySQL 8.0

    The following flags were added for ApsaraDB RDS for MySQL 8.0, grouped by the minor engine version that introduced them.

    Flag

    Minor engine version

    use_invisible_indexes={on|off}

    rds_20190601

    duplicateweedout={on|off}

    rds_20200331

    condition_fanout_filter={on|off}

    rds_20200331

    derived_merge={on|off}

    rds_20200331

    skip_scan={on|off}

    rds_20200331

    hash_join={on|off}

    rds_20200331

    subquery_to_derived={on|off}

    rds_20201231

    prefer_ordering_index={on|off}

    rds_20201231

    derived_condition_pushdown={on|off}

    rds_20201231

    MySQL 5.7

    The following flags were added for ApsaraDB RDS for MySQL 5.7.

    Flag

    Minor engine version

    condition_fanout_filter={on|off}

    rds_20190915

    derived_merge={on|off}

    rds_20190915

    duplicateweedout={on|off}

    rds_20190915

    prefer_ordering_index={on|off}

    rds_20211231

    MySQL 5.6

    All minor versions of MySQL 5.6 support the following flags.

    Flag

    Minor engine version

    batched_key_access={on|off}

    All

    block_nested_loop={on|off}

    engine_condition_pushdown={on|off}

    firstmatch={on|off}

    index_condition_pushdown={on|off}

    index_merge={on|off}

    index_merge_intersection={on|off}

    index_merge_sort_union={on|off}

    index_merge_union={on|off}

    loosescan={on|off}

    materialization={on|off}

    mrr={on|off}

    mrr_cost_based={on|off}

    semijoin={on|off}

    subquery_materialization_cost_based={on|off}

    use_index_extensions={on|off}

    Modify the parameter

    To modify the loose_optimizer_switch parameter through the ApsaraDB RDS console, see Modify the parameters of an ApsaraDB RDS for MySQL instance.

    Verify current settings

    To view the current optimizer_switch value on your RDS instance, run the following SQL statement:

    SELECT @@optimizer_switch\G

    After modifying a flag, verify the change took effect:

    SHOW VARIABLES LIKE 'optimizer_switch';

    References