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.
