DISTINCT Optimization for Multi-Table Joins

Scenarios

• Nested Loop Inner Join + Distinct
• Nested Loop Outer Join + Distinct

Constraints

This feature is only available when the kernel version is 2.0.51.240300 or later.

Enabling DISTINCT Optimization for Multi-Table Joins

You can also use hints to enable or disable DISTINCT optimization. The syntax is as follows:

• Enabling DISTINCT optimization

  /*+ SET_VAR(rds_nlj_distinct_optimize=ON) */

• Disabling DISTINCT optimization

  /*+ SET_VAR(rds_nlj_distinct_optimize=OFF) */

Example

1. Use either of the following methods to enable DISTINCT optimization:
   • Run the SET command to set the switch value.

     mysql> SET rds_nlj_distinct_optimize=ON;
     Query OK, 0 rows affected (0.00 sec)
     
     mysql> SET rds_nlj_distinct_optimize=OFF;
     Query OK, 0 rows affected (0.00 sec)

   • Use hints to set the switch value in SQL statements.

     mysql> EXPLAIN ANALYZE SELECT/*+ SET_VAR(rds_nlj_distinct_optimize=ON) */ DISTINCT tt1.a FROM t1 AS tt1 JOIN t1 AS tt2 JOIN t1 AS tt3 ON tt2.a + 3 = tt3.a;
     
     mysql> EXPLAIN ANALYZE SELECT/*+ SET_VAR(rds_nlj_distinct_optimize=OFF) */ DISTINCT tt1.a FROM t1 AS tt1 JOIN t1 AS tt2 JOIN t1 AS tt3 ON tt2.a + 3 = tt3.a;

2. Check the DISTINCT optimization effect in the multi-table join scenario.

   Run the Explain Analyze/Explain Format=tree statement to check whether the optimization is applied. If the execution plan contains keyword with distinct optimization, the optimization is applied.

   The detailed procedure is as follows:

   1. Prepare data.

      CREATE TABLE t1(a INT, KEY(a));
      INSERT INTO t1 VALUES(1),(2),(5),(6),(7),(8),(9),(11);
      ANALYZE TABLE t1;

   2. Disable the feature and run the following SQL statements. The optimizer chooses the default execution plan.

      mysql> SET rds_nlj_distinct_optimize=OFF;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> EXPLAIN FORMAT=TREE SELECT DISTINCT tt1.a FROM t1 AS tt1 LEFT JOIN t1 AS tt2 ON TRUE LEFT JOIN t1 AS tt3 ON tt2.a + 3 = tt3.a\G
      *************************** 1. row ***************************
      EXPLAIN: -> Table scan on <temporary>
          -> Temporary table with deduplication  (cost=29.18 rows=64)
              -> Nested loop left join  (cost=29.18 rows=64)
                  -> Left hash join (no condition)  (cost=6.78 rows=64)
                      -> Index scan on tt1 using a  (cost=1.05 rows=8)
                      -> Hash
                          -> Index scan on tt2 using a  (cost=0.13 rows=8)
                  -> Filter: ((tt2.a + 3) = tt3.a)  (cost=0.25 rows=1)
                      -> Index lookup on tt3 using a (a=(tt2.a + 3))  (cost=0.25 rows=1)

   3. Enable the feature and run the following SQL statements. The execution plan contains keyword with distinct optimization, which indicates that the optimization is applied.

      mysql> SET rds_nlj_distinct_optimize=ON;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> EXPLAIN FORMAT=TREE SELECT DISTINCT tt1.a FROM t1 AS tt1 LEFT JOIN t1 AS tt2 ON TRUE LEFT JOIN t1 AS tt3 ON tt2.a + 3 = tt3.a\G
      *************************** 1. row ***************************
      EXPLAIN: -> Table scan on <temporary>
          -> Temporary table with deduplication  (cost=29.18 rows=64)
              -> Nested loop left join with distinct optimization  (cost=29.18 rows=64)
                  -> Left hash join (no condition)  (cost=6.78 rows=64)
                      -> Index scan on tt1 using a  (cost=1.05 rows=8)
                      -> Hash
                          -> Index scan on tt2 using a  (cost=0.13 rows=8)
                  -> Filter: ((tt2.a + 3) = tt3.a)  (cost=0.25 rows=1)
                      -> Index lookup on tt3 using a (a=(tt2.a + 3))  (cost=0.25 rows=1)

Performance Test

GaussDB(for MySQL) completed the execution in 2.7s and scanned only about 610,000 rows of data. This is a significant improvement in execution efficiency compared to MySQL 8.0, which completed the execution in 186s and scanned 44 million rows of data.

In the following example, when performing a DISTINCT operation on the results after 7 tables were joined, MySQL 8.0.30 took 186s to execute and scanned about 44 million rows of data,

while GaussDB(for MySQL) only took 2.7s and scanned about 610,000 rows of data.

Query statement:

select distinct ed.code,et.*
from ele_template et
left join ele_template_tenant ett on ett.template_id = et.id
left join ele_relation tm on tm.tom_id = et.id and tm.jerry_type = 'chapter'
left join ele_relation mv on mv.tom_id = tm.jerry_id and mv.jerry_type = 'variable'
left join ele_relation cv on cv.jerry_id = mv.jerry_id and cv.tom_type = 'column'
left join ele_doc_column edc on edc.id = cv.tom_id
left join ele_doc ed on ed.id = edc.doc_id
where ett.uctenantid = 'mmo0l3f8'
and ed.code = 'contract'
and et.billtype = 'contract'
order by ifnull(et.utime,et.ctime)
desc limit 0,10;