Bug #115679 Block Nested-Loops Join can sometimes slow down the query
Submitted: 24 Jul 2024 9:04 Modified: 24 Jul 2024 9:39
Reporter: hongjun xiao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.37 OS:Any
Assigned to: CPU Architecture:Any
Tags: BNL, cost, Optimizer hints

[24 Jul 2024 9:04] hongjun xiao 
Description:
the join buffer slows down the query.This could lead to potential performance issues.
This is a simple use case：

mysql> explain analyze SELECT DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.26..3.53 rows=2) (actual time=0.0277..0.0279 rows=1 loops=1)
    -> Temporary table with deduplication  (cost=1..1 rows=2) (actual time=0.0273..0.0273 rows=1 loops=1)
        -> Inner hash join (no condition)  (cost=0.8 rows=2) (actual time=0.0173..0.0189 rows=2 loops=1)
            -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00238..0.00347 rows=2 loops=1)
            -> Hash
                -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.00801..0.00967 rows=1 loops=1)
 |

-- When not using the join buffer, both the cost and the actual time are reduced.

mysql> explain analyze SELECT /*+ NO_BNL(t1, t0)*/ DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates)  (cost=1 rows=1.41) (actual time=0.0131..0.0132 rows=1 loops=1)
    -> Nested loop inner join  (cost=0.8 rows=2) (actual time=0.00888..0.0114 rows=2 loops=1)
        -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.00595..0.00658 rows=1 loops=1)
        -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00193..0.00356 rows=2 loops=1)
 |
mysql> EXPLAIN SELECT DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                         |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------------------------+
|  1 | SIMPLE      | t0    | NULL       | index | c10           | c10  | 5       | NULL |    1 |   100.00 | Using index; Using temporary  |
|  1 | SIMPLE      | t1    | NULL       | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT /*+ NO_BNL(t1, t0)*/ DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t0    | NULL       | index | c10           | c10  | 5       | NULL |    1 |   100.00 | Using index |
|  1 | SIMPLE      | t1    | NULL       | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

How to repeat:
CREATE TABLE t0(c0 INT, c10 INT  UNIQUE );
INSERT INTO t0(c10) VALUES(1);
CREATE TABLE t1(c6 INT , c10 INT) ;
INSERT INTO t1(c6, c10) VALUES(8, 1);
INSERT INTO t1(c6, c10) VALUES(7, 2);

ANALYZE TABLE t0;
ANALYZE TABLE t1;
explain analyze SELECT DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10; 
explain analyze SELECT /*+ NO_BNL(t1, t0)*/ DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10; 

-- here are the results：

mysql> explain analyze SELECT DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.26..3.53 rows=2) (actual time=0.0586..0.059 rows=1 loops=1)
    -> Temporary table with deduplication  (cost=1..1 rows=2) (actual time=0.0578..0.0578 rows=1 loops=1)
        -> Inner hash join (no condition)  (cost=0.8 rows=2) (actual time=0.0396..0.043 rows=2 loops=1)
            -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00467..0.00717 rows=2 loops=1)
            -> Hash
                -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.0221..0.026 rows=1 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.26..3.53 rows=2) (actual time=0.0825..0.0828 rows=1 loops=1)
    -> Temporary table with deduplication  (cost=1..1 rows=2) (actual time=0.0815..0.0815 rows=1 loops=1)
        -> Inner hash join (no condition)  (cost=0.8 rows=2) (actual time=0.0561..0.0605 rows=2 loops=1)
            -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00681..0.0101 rows=2 loops=1)
            -> Hash
                -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.0307..0.0356 rows=1 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (cost=2.26..3.53 rows=2) (actual time=0.0537..0.0539 rows=1 loops=1)
    -> Temporary table with deduplication  (cost=1..1 rows=2) (actual time=0.053..0.053 rows=1 loops=1)
        -> Inner hash join (no condition)  (cost=0.8 rows=2) (actual time=0.0367..0.0392 rows=2 loops=1)
            -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00412..0.00598 rows=2 loops=1)
            -> Hash
                -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.0213..0.0244 rows=1 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT /*+ NO_BNL(t1, t0)*/ DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates)  (cost=1 rows=1.41) (actual time=0.0393..0.0394 rows=1 loops=1)
    -> Nested loop inner join  (cost=0.8 rows=2) (actual time=0.0297..0.0362 rows=2 loops=1)
        -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.0227..0.0239 rows=1 loops=1)
        -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.0052..0.00987 rows=2 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT /*+ NO_BNL(t1, t0)*/ DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates)  (cost=1 rows=1.41) (actual time=0.0407..0.0409 rows=1 loops=1)
    -> Nested loop inner join  (cost=0.8 rows=2) (actual time=0.0306..0.0375 rows=2 loops=1)
        -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.0233..0.0246 rows=1 loops=1)
        -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00488..0.00993 rows=2 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain analyze SELECT /*+ NO_BNL(t1, t0)*/ DISTINCT t0.c10 FROM t0, t1 GROUP BY t0.c10;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates)  (cost=1 rows=1.41) (actual time=0.0512..0.0513 rows=1 loops=1)
    -> Nested loop inner join  (cost=0.8 rows=2) (actual time=0.0384..0.047 rows=2 loops=1)
        -> Covering index scan on t0 using c10  (cost=0.35 rows=1) (actual time=0.0293..0.0314 rows=1 loops=1)
        -> Table scan on t1  (cost=0.45 rows=2) (actual time=0.00695..0.0129 rows=2 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[24 Jul 2024 9:39] MySQL Verification Team 
Hello hongjun xiao,

Thank you for the report and test case.

regards,
Umesh