PolarDB:Specify join reorder for the IMCI optimizer

How it works

SQL is a declarative language: a single query can be executed in many ways, all producing the same correct result. The query optimizer explores this space of equivalent query plans and picks the one with the lowest estimated cost.

For example, the following query on four tables can be executed in at least two orders:

SELECT * FROM t0, t1, t2, t3
WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

Plan A and Plan B produce the same result. The optimizer generates Plan B by applying a query transformation rule — for example, rewriting t1 INNER JOIN t2 as t2 INNER JOIN t1. It then estimates the cost of each plan and selects the one with the lower cost.

Cost(join) = Card(inner) + Card(outer)

Supported versions

Your PolarDB cluster must be running one of the following versions:

• PolarDB for MySQL 8.0.1, revision 8.0.1.1.31 or later

• PolarDB for MySQL 8.0.2, revision 8.0.2.2.12 or later

Enable IMCI query optimization

Parameters

Configure the following parameters in the PolarDB console to control IMCI query optimization.

Limitations and workarounds

The following conditions can cause large errors in cardinality estimation, leading the optimizer to pick a suboptimal plan. When you encounter one of these conditions, use a JOIN_ORDER hint to explicitly specify the join order.

Applying a `JOIN_ORDER` hint: add the hint directly in the SELECT statement to override the optimizer's join order. For example:

SELECT /*+ JOIN_ORDER(t0, t1, t2, t3) */ *
FROM t0, t1, t2, t3
WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

For the full list of supported join order hints, see MySQL optimizer hints for join order.

Performance results

The following example uses TPC-H Q8, a multi-table query with aggregate functions representative of complex OLAP workloads.

SELECT
  o_year,
  SUM(
    CASE
      WHEN nation = 'BRAZIL' THEN volume
      ELSE 0
    END
  ) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(year FROM o_orderdate) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      lineitem, orders, part, supplier, customer, nation n1, nation n2, region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
      AND p_type = 'ECONOMY ANODIZED STEEL'
  ) AS all_nations
GROUP BY o_year
ORDER BY o_year;

Test environment: 32-core cluster, TPC-H SF100 dataset.

Without IMCI query optimization

The query plan produces large intermediate result sets across multiple joins, increasing the rows processed by each operator. Query duration: 7,017 ms.

With IMCI query optimization

The optimizer reorders joins to reduce intermediate result set sizes to the order of millions of rows, cutting the work for downstream operators. Query duration: 1,900 ms — a 73% reduction.

Related topics

• Specify cluster and node parameters

• MySQL optimizer hints for join order