Bug #70553 EXPLAIN UPDATE shows "Using join buffer" while it is not used
Description:
EXPLAIN UPDATE shows "Using join buffer" where actual execution does not use join buffering.
How to repeat:
Run these commands:
create table t1 (a int);
insert into t1 values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, b int);
insert into t2 select a, a from t1;
insert into t2 select a+10, a+10 from t1;
explain update t1, t2 set t2.b=12345 where t1.a=t2.a;
MySQL [j1]> explain update t1, t2 set t2.b=12345 where t1.a=t2.a;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
| 1 | UPDATE | t2 | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
^^ EXPLAIN shows that join buffering will be used.
MySQL [j1]> flush status;
Query OK, 0 rows affected (0.00 sec)
MySQL [j1]> update t1, t2 set t2.b=12345 where t1.a=t2.a;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
MySQL [j1]> show status like 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 10 |
| Handler_read_rnd_next | 232 |
^^ The value of Handler_read_rnd_next, 232 ~= 10*20 suggest that the join buffering was not used for execution. Debugging also shows that join buffering is not used for the actual UPDATE statement.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.