Bug #72895 single-table UPDATE/DELETE should use subquery optimizations

• Submitted: 6 Jun 2014 7:30
• Modified: 20 Apr 2020 22:10
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[6 Jun 2014 7:30] Guilhem Bichot

Description:
Since the introduction of semijoin and subquery materialization in 5.6, those features have always been disabled if the statement is single-table UPDATE/DELETE. This results in missed opportunities. For example:
update t1 set a=3 where (b,c) in (select d, max(e) from t2 group by d);
It can be faster to calculate the result of the subquery once for all (i.e. use subquery materialization), instead of using IN->EXISTS (as is done now) which results
in one subquery evaluation per row of t1.

How to repeat:
create table t1(a int);
create table t2(a int);

Compare:
explain select * from t1 where a in (select * from t2);
to:
explain update t1 set a=a+2 where a in (select * from t2);

[20 Apr 2020 14:55] Guilhem Bichot

Posted by developer:
 
Fixed by WL#6057. Single-table UPDATE/DELETE can now use subquery materialization and semijoin strategies.

[20 Apr 2020 22:10] Jon Stephens

Fixed in MySQL 8.0.21 by WL#6057; see same for docs info.

Closed.