| Bug #78397 | Subquery Materialization on DELETE WHERE IN (SELECT) | ||
|---|---|---|---|
| Submitted: | 10 Sep 2015 12:04 | Modified: | 10 Sep 2015 12:26 |
| Reporter: | Jervin R | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.6.26, 5.7.9, 8.0.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Sep 2015 12:26]
MySQL Verification Team
Hello Jervin, Thank you for the report and test case. Verified as described on 5.6.26. Thanks, Umesh
[10 Sep 2015 12:27]
MySQL Verification Team
// 5.6.26
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.26 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.26-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)
mysql> create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t (c) values ('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table n (id int unsigned not null primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into n values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain delete from t where id in (select id from n) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: n
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
mysql> explain select * from t where id in (select id from n) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: n
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set (0.00 sec)
[10 Sep 2015 12:32]
MySQL Verification Team
// 5.7.9, 5.8.0
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.9 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.9-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
mysql> create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t (c) values ('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table n (id int unsigned not null primary key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into n values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain delete from t where id in (select id from n) \G
*************************** 1. row ***************************
id: 1
select_type: DELETE
table: t
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: n
partitions: NULL
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set (0.00 sec)
mysql> explain select * from t where id in (select id from n) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: n
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
// 5.8.0
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.8.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.8.0-m17-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t (c) values ('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table n (id int unsigned not null primary key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into n values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain delete from t where id in (select id from n) \G
*************************** 1. row ***************************
id: 1
select_type: DELETE
table: t
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: n
partitions: NULL
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set (0.00 sec)
mysql> explain select * from t where id in (select id from n) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: n
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
[11 Sep 2015 8:40]
Øystein Grøvlen
Semi-join optimization is not performed for single-table update/delete statements. However, there is a work-around: Rewrite query as multi-table delete. See http://mysqlserverteam.com/multi-table-trick/ for an example.
[18 Jun 2016 21:27]
Omer Barnir
Posted by developer: Reported version value updated to reflect release name change from 5.8 to 8.0
Description: With queries DELETE FROM tbl WHERE col IN (SELECT col FROM tbl2), the subquery is not materialized unlike it SELECT counterpart. How to repeat: [revin@acme mysql]$ msb 5626 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.26 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost] {msandbox} ((none)) > use test; Database changed mysql [localhost] {msandbox} (test) > select @@optimizer_switch -> \G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into t (c) values ('a'), ('b'), ('c'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > create table n (id int unsigned not null primary key) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > insert into n values (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > explain delete from t where id in (select id from n) \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: n type: unique_subquery possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: func rows: 1 Extra: Using index 2 rows in set (0.01 sec) mysql [localhost] {msandbox} (test) > explain select * from t where id in (select id from n) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: n type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where; Using join buffer (Block Nested Loop) 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) >