Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table
SELECT * FROM ( Q ) t
|
is always executed without semi-join optimization.
In a general case any merge of a derived table / view blocks usage of semi-join optimizations
for it.
Here' s a test case that demonstrate this problem.
create table t1 (a int) engine=myisam;
|
insert into t1 values (5),(3),(2),(7),(2),(5),(1);
|
create table t2 (b int, index idx(b)) engine=myisam;
|
insert into t2 values (2),(3),(2),(1),(3),(4);
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
insert into t2 select b+10 from t2;
|
analyze table t1,t2;
|
explain select a from t1 where a in (select b from t2);
|
explain select * from (select a from t1 where a in (select b from t2)) t;
|
For the EXPLAINs from the testcase the following is returned:
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
|
| 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 140 | Using index; FirstMatch(t1) |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|
 |
MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t;
|
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
|
| 3 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 140 | Using index |
|
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
|
This is not expected as the merge transformation applied to t converts Q1 to Q.