Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1, 10.2
-
None
Description
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
|
select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
|
However their execution plans are different:
MariaDB [test]> explain
|
-> select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+
|
| 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 | 256 | Using index; FirstMatch(t1) |
|
| 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 2 | Using where; Using index; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+
|
 |
MariaDB [test]> explain
|
-> select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
|
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 2 | Using where; Using index |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 256 | Using index |
|
+------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
|
The problem can be reproduced with the following test case.
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;
|
insert into t2 select b+10 from t2;
|
analyze table t1,t2;
|
explain select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
|
explain select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
|