Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL)
Description
Test dataset:
create table ten(a int); |
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table ten2 as select * from ten; |
|
|
create table t1(a int, b int, key(a)); |
insert into t1 |
select |
A.a + B.a* 10 + C.a * 100,
|
A.a + B.a* 10 + C.a * 100
|
from |
ten A, ten B, ten C;
|
|
|
create table t2 like t1; |
insert into t2 select * from t1; |
The query is constructed as follows:
- a non-reducible nested outer join (multiple tables on the inner side)
- the nested outer join has a semi-join inside it
- the data distribution is such that an inner-to-outer join order inside the
join nest would be beneficial: putting the subquery first would allow to use
a good index on t2.a, and read 10*1=10 rows from there as opposed to 1K rows
it has in total
explain
|
select * |
from |
ten left join |
(t1, t2) on (t2.a in (select ten2.a from ten2) and |
t2.b=t1.b and |
t1.b=ten.a);
|
Explain:
+------+--------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| 1 | PRIMARY | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 760 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 1088 | Using where; Using join buffer (incremental, BNL join) |
|
| 2 | MATERIALIZED | ten2 | ALL | NULL | NULL | NULL | NULL | 10 | |
|
+------+--------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
We can see that ten2.id=2, that is, the subquery was not converted into a semi-join.
EXPLAIN in mysql 8:
explain select * from ten left join (t1, t2) on (t2.a in (select ten2.a from ten2) and t1.b=t2.b and t1.b=ten.a);
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+------------------------------+
|
| 1 | SIMPLE | ten | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
|
| 1 | SIMPLE | ten2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Start temporary |
|
| 1 | SIMPLE | t2 | NULL | ref | a | a | 5 | j2.ten2.a | 1 | 100.00 | Using where; End temporary |
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where |
|
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+------------------------------+
|
here ten2.id=1, and it is put before the table t2, which has t2.type=ref with rows=1 (efficient!)