[MDEV-7487] Semi-join optimization for single-table update/delete statements Created: 2015-01-21  Updated: 2023-10-24  Resolved: 2023-03-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: 11.1.0

Type: Task Priority: Critical
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 5
Labels: Preview_10.11, Preview_11.1, optimizer, subquery

Issue Links:
Duplicate
duplicates MDEV-15385 Full scan instead of index lookup on ... Closed
is duplicated by MDEV-15385 Full scan instead of index lookup on ... Closed
PartOf
is part of MDEV-29547 prepare 10.11.0 preview releases Closed
Relates
relates to MDEV-22415 Single table UPDATE/DELETE doesn't us... Open
relates to MDEV-10447 different execution plan for single- ... Open
relates to MDEV-14956 Slow deletes if number of deleted row... Closed
relates to MDEV-18561 Semi-Join optimization for single-tab... Closed

 Description   

Currently, single-table UPDATE/DELETE statements cannot take advantage of semi-join optimizations. This is because semi-join optimizations are parts of join optimization (which is not invoked for single-table UPDATE/DELETE).

This task is about

  • detecting UPDATE/DELETEs that can use semi-join optimizations
  • switching those queries to be processed as multi-table UPDATE/DELETEs. This will enable semi-join optimizations.

Note that we do similar things for VIEWs: UPDATE multi_table_view SET =... is switched to be processed as multi-table UPDATE as soon as the server discovers that we're updating a multi-table VIEW.

 Comments   
Comment by Sergei Petrunia [ 2015-02-20 ]

See also MDEV-7220 for a related discussion. Non-semijoin optimizations (e.g. Materialization vs IN->EXISTS) would are not used in single-table UPDATE/DELETE, either. And that is a deficiency, too.

Comment by Oleksandr Byelkin [ 2022-06-20 ]

OK to push

Comment by Lena Startseva [ 2023-01-18 ]

In branch bb-10.11-MDEV-7487rebased-against-11.0MDEV-29390-test

  • Created tests on update and delete:
    Engine Update (update_use_source.test) Delete (delete_use_source_engins.test)
    InnoDB
    Aria
    MyISAM
    MEMORY
  • Added tests for update/delete with LooseScan and DuplicateWeedout optimization strategies (in delete_single_to_multi.test, update_single_to_multi.test)
  • Added test for multi-update with JSON_TABLE (in mysql-test/suite/json/t/json_table.test)
Comment by Lena Startseva [ 2023-01-18 ]

With scripts there were created tests based on "main" suite to check for a server crash on update. Example of converting:
this

SELECT 1 FROM t2 WHERE pk > ANY (SELECT 1 FROM t2);

converts to

create or replace table ut_tmp (a int);
 
update ut_tmp, t2 set ut_tmp.a = 10 WHERE pk > ANY (SELECT 1 FROM );
 
drop table ut_tmp;

During the tests execution there was only one crash related to the parameter:

set debug_dbug="+d,cache_sp_in_show_create";

But this parameter ("cache_sp_in_show_create")created only for tests with special behavior in lock_sync.test so there were no null-pointer checks and that's why I got this crach.

Comment by Lena Startseva [ 2023-01-23 ]

Checked engine with update/delete tests:

Engine main.delete main.delete_single_to_multi main.update main.update_single_to_multi main.view comments
InnoDB In main.view was problem like MDEV-8632, but it repeats from 10.3
Aria  
MyISAM  
RocksDB  
MEMORY O O  

In branch bb-10.11-MDEV-7487rebased-against-11.0MDEV-29390-test:

  • Added test for multi-update with JSON_TABLE
  • Added tests for multi-update and multi-delete for engine Connect

Tables with versioning:

  • tables with time period do not support multi-update (there is a case in period.update)
  • checked update/delete with tests from update.test, update_single_to_multi.test, delete.test, delete_single_to_multi.test - in these cases for all possible tables were added "with system versioning"

Engine Spider check with small tests for (multi-)update/delete

Comment by Lena Startseva [ 2023-02-14 ]

Testing of this task is completed. The latest version of the tests can be taken from the branch bb-10.11-MDEV-7487rebased-against-11.0MDEV-29390-test-2
At the moment, there is not found critical bugs associated with this implementation, but there are bugs that are a legacy of older versions (e.g. MDEV-30539). Bugs like this and low test coverage can give potential risks (especially after merge).

Comment by Igor Babaev [ 2023-03-16 ]

The commit for this task was pushed into 11.1

Generated at Thu Feb 08 07:19:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.