Details
Major Description
For the following query with SELECT:
explain
|
select * |
from v1,t3 |
where
|
v1.c2 = t3.c2 and |
v1.c2 in (select max(c2) from t2 where c3 = v1.c3) |
and v1.c1 < 10 and |
exists (select 'X' from v2 where c1 = v1.c1); |
EXPLAIN produces:
MariaDB [test]> explain
|
-> select *
|
-> from v1,t3
|
-> where
|
-> v1.c2 = t3.c2 and
|
-> v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
|
-> and v1.c1 < 10 and
|
-> exists (select 'X' from v2 where c1 = v1.c1);
|
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
|
| 1 | PRIMARY | t1 | ref | t1_c2 | t1_c2 | 5 | const | 2 | Using where |
|
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 8 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | MATERIALIZED | t2 | ref | t2_c2 | t2_c2 | 5 | const | 2 | |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+-------+------+-------------------------------------------------+
|
For the queries with DELETE and UPDATE based on previous SELECT:
delete
|
from v1 |
using v1,t3
|
where
|
v1.c2 = t3.c2 and |
v1.c2 in (select max(c2) from t2 where c3 = v1.c3) |
and v1.c1 < 10 and |
exists (select 'X' from v2 where c1 = v1.c1); |
update v1,t3 |
set v1.c1 = 15 |
where
|
v1.c2 = t3.c2 and |
v1.c2 in (select max(c2) from t2 where c3 = v1.c3) |
and v1.c1 < 10 and |
exists (select 'X' from v2 where c1 = v1.c1); |
EXPLAIN produces:
MariaDB [test]> explain
|
-> delete
|
-> from v1
|
-> using v1,t3
|
-> where
|
-> v1.c2 = t3.c2 and
|
-> v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
|
-> and v1.c1 < 10 and
|
-> exists (select 'X' from v2 where c1 = v1.c1);
|
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
|
| 1 | PRIMARY | t1 | ref | t1_c2 | t1_c2 | 5 | const | 2 | Using where |
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
|
| 3 | DEPENDENT SUBQUERY | t2 | ref | t2_c2 | t2_c2 | 5 | const | 2 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
|
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
|
 |
MariaDB [test]> explain
|
-> update v1,t3
|
-> set v1.c1 = 15
|
-> where
|
-> v1.c2 = t3.c2 and
|
-> v1.c2 in (select max(c2) from t2 where c3 = v1.c3)
|
-> and v1.c1 < 10 and
|
-> exists (select 'X' from v2 where c1 = v1.c1);
|
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
|
| 1 | PRIMARY | t1 | ref | t1_c2 | t1_c2 | 5 | const | 2 | Using where |
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
|
| 3 | DEPENDENT SUBQUERY | t2 | ref | t2_c2 | t2_c2 | 5 | const | 2 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
|
+------+--------------------+-------+------+---------------+-------+---------+-------+------+-------------+
|
|
but it is expected that the plan will be similar to the plan for SELECT
MTR test case:
 |
create table t1 (c1 integer, c2 integer, c3 integer) ; |
insert into t1(c1,c2,c3) |
values (1,1,1),(1,2,2),(1,3,3), |
(2,1,4),(2,2,5),(2,3,6),
|
(2,4,7),(2,5,8);
|
create view v1 as select * from t1 where c2=2; |
create index t1_c2 on t1 (c2); |
analyze table t1 persistent for all; |
 |
create table t2 (c1 integer, c2 integer, c3 integer) ; |
insert into t2(c1,c2,c3) |
values (1,1,1),(1,2,2),(1,3,3), |
(2,1,4),(2,2,5),(2,3,6),
|
(2,4,7),(2,5,8);
|
create view v2 as select * from t2 where c2=2; |
create index t2_c2 on t2 (c2); |
analyze table t2 persistent for all; |
 |
create table t3 (c1 integer, c2 integer, c3 integer) ; |
insert into t3(c1,c2,c3) |
values (1,1,1),(1,2,2),(1,3,3), |
(2,1,4),(2,2,5),(2,3,6),
|
(2,4,7),(2,5,8);
|
analyze table t3 persistent for all; |
 |
explain
|
select * |
from v1,t3 |
where
|
v1.c2 = t3.c2 and |
v1.c2 in (select max(c2) from t2 where c3 = v1.c3) |
and v1.c1 < 10 and |
exists (select 'X' from v2 where c1 = v1.c1); |
|
explain
|
delete
|
from v1 |
using v1,t3
|
where
|
v1.c2 = t3.c2 and |
v1.c2 in (select max(c2) from t2 where c3 = v1.c3) |
and v1.c1 < 10 and |
exists (select 'X' from v2 where c1 = v1.c1); |
 |
explain
|
update v1,t3 |
set v1.c1 = 15 |
where
|
v1.c2 = t3.c2 and |
v1.c2 in (select max(c2) from t2 where c3 = v1.c3) |
and v1.c1 < 10 and |
exists (select 'X' from v2 where c1 = v1.c1); |
 |
drop view v1, v2; |
drop table t1, t2, t3; |