[MDEV-18561] Semi-Join optimization for single-table update Created: 2019-02-12  Updated: 2023-08-29  Resolved: 2023-08-24

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Fix Version/s: 11.1.0

Type: Task Priority: Major
Reporter: Matthias Schröder Assignee: Igor Babaev
Resolution: Duplicate Votes: 0
Labels: optimizer, performance

Attachments: File patch.diff    
Issue Links:
Relates
relates to MDEV-7487 Semi-join optimization for single-tab... Closed

 Description   

It seems better to switch to multi_table_update code if subqueries are involved in order to gain from semi-join optimization:

Before

MariaDB [test_real]> explain UPDATE akte set ckunr = concat('X',trim(ckunr)) where naktnr IN (SELECT naktnr FROM imp_adr where cdiskid='D_0000010561518');

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY akte ALL NULL NULL NULL NULL 8559040 Using where
2 DEPENDENT SUBQUERY imp_adr ref naktnr,cdiskid cdiskid 15 const 328 Using where with pushed condition
After

MariaDB [myads]> explain UPDATE akte set ckunr = concat('X',trim(ckunr)) where naktnr IN (SELECT naktnr FROM imp_adr where cdiskid='D_0000010561518');

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 328  
1 PRIMARY akte eq_ref naktnr naktnr 4 myads.imp_adr.naktnr 1  
2 MATERIALIZED imp_adr ref naktnr,cdiskid cdiskid 15 const 328 Using where with pushed condition

I am on the mariadb-server 10.3 branch, latest code.

 Comments   
Comment by Sergei Petrunia [ 2023-08-24 ]

Fixed in 11.1 by fix for MDEV-7487.

Generated at Thu Feb 08 08:45:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.