|
Table elimination is not applied inside semi-joins. This has adverse effects in a number of cases.
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table one_k(a int);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
|
create table t0 (a int, b int, c int);
|
insert into t0 select a,a,a from ten;
|
|
create table t1 (a int, b int, c int);
|
insert into t1 select a,a,a from ten;
|
|
create table t2 (pk int, b int, c int, primary key(pk));
|
insert into t2 select a,a,a from one_k;
|
|
create table t3 (pk int, b int, c int, primary key(pk));
|
insert into t3 select a,a,a from one_k;
|
explain select * from t0
|
where t0.a in (
|
select t1.a
|
from t1
|
left join t2 on t2.pk=t1.b
|
left join t3 on t3.pk=t1.b
|
);
|
Gives:
+------+-------------+-------+--------+---------------+---------+---------+----------+------+------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+----------+------+------------------------------------------------------------------+
|
| 1 | PRIMARY | t0 | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Start temporary; Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | j22.t1.b | 1 | Using where; Using index |
|
| 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | j22.t1.b | 1 | Using where; Using index; End temporary |
|
+------+-------------+-------+--------+---------------+---------+---------+----------+------+------------------------------------------------------------------+
|
An easy way to check that table elimination is applicable:
set optimizer_switch='semijoin=off';
|
explain ...
|
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | PRIMARY | t0 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
|
|