All Products
Search

This Product

    PolarDB:Left join elimination

    Document Center

    PolarDB:Left join elimination

    Last Updated:Mar 30, 2026

    Highly normalized schemas and view-based query layers often generate SQL with many LEFT JOIN clauses, most of which are logically redundant — the joined table contributes nothing to the result set. Left join elimination removes these redundant joins at the optimizer level, so the database reads only the tables it actually needs. For complex analytical queries, this can reduce execution time by an order of magnitude without any changes to your SQL.

    How it works

    For each LEFT JOIN in a query, PolarDB for MySQL checks two conditions:

    1. Only one row in the right table can meet the join condition for each row in the left table.

    2. No column from the right table appears in the SELECT list or anywhere else in the query.

    If both conditions are met, PolarDB removes the join from the execution plan. The optimizer applies this check to every LEFT JOIN in the query, so multiple redundant joins can be eliminated.

    Prerequisites

    Before you begin, ensure that you have:

    • A PolarDB for MySQL 8.0 cluster with revision version 8.0.1.1.32 or later, or 8.0.2.2.10 or later

    Limitations

    Left join elimination applies only when both of the following conditions are met:

    • For each row in the left table, only one row in the right table can meet the join condition.

    • No column from the right table is referenced anywhere in the SQL statement outside the LEFT JOIN clause itself.

    Enable left join elimination

    Set the loose_join_elimination_mode parameter to control when the feature is active.

    Parameter Level Description
    loose_join_elimination_mode Global Controls left join elimination. Default value: REPLICA_ON. Valid values: ON (enabled on all nodes), REPLICA_ON (enabled on read-only nodes only), OFF (disabled).

    To change the parameter, see Specify cluster and node parameters.

    Note

    The default value REPLICA_ON enables the feature only on read-only nodes. To enable it on all nodes, including the primary node, set the parameter to ON.

    Verify with EXPLAIN

    Use EXPLAIN to confirm that left join elimination is applied to your query.

    Before optimization

    The following query joins table1 (alias sc), table2 (alias ca), and table3 (alias co). Each row in table1 matches only one row in table2 and table3, and no columns from table2 or table3 appear in the SELECT list — both elimination conditions are met.

    EXPLAIN
SELECT count(*)
FROM `table1` `sc`
  LEFT JOIN `table2` `ca` ON `sc`.`car_id` = `ca`.`id`
  LEFT JOIN `table3` `co` ON `sc`.`company_id` = `co`.`id`;

    Output:

    +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | sc    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | ca    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | je_test.sc.car_id     |    1 |   100.00 | Using index |
|  1 | SIMPLE      | co    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | je_test.sc.company_id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+

    All three tables appear in the plan. Execution time: 7.5 seconds.

    After optimization

    With left join elimination enabled, the optimizer detects that the joins to table2 and table3 are redundant and rewrites the query to scan only table1.

    EXPLAIN
SELECT count(*)
FROM `table1` `sc`

    Output:

    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

    Only table1 remains in the plan. Execution time: 0.1 seconds — 75x faster than the original query.

    Related topics