Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
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);
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Environment |
These two join queries use just two different form forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ 3 rows in set (8.19 sec) 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case: {noformat} {noformat} |
| Description |
These two join queries use just two different form forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ 3 rows in set (8.19 sec) 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case: {noformat} {noformat} 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 * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
| Description |
These two join queries use just two different form forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ 3 rows in set (8.19 sec) 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case: {noformat} {noformat} 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 * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
These two join queries use just two different form forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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 * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
| Description |
These two join queries use just two different form forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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 * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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 * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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 * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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 values (12),(13),(12),(11),(13),(14); insert into t2 values (22),(23),(22),(21),(23),(24); insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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 values (12),(13),(12),(11),(13),(14); insert into t2 values (22),(23),(22),(21),(23),(24); insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; iexplain 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; iexplain 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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; 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 insert into t2 select b+10 from t2; insert into t2 select b+10 from t2; | +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | ref | idx | idx | 5 | test.t1.a | 192 | Using index; FirstMatch(t1) | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | 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 | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 1 | PRIMARY | t2 | range | idx | idx | 5 | NULL | 6852 | Using where; Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t2 | index_subquery | idx | idx | 5 | func | 192 | Using index | +------+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+ noformat} The problem can be reproduced with the following test case. {noformat} 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); {noformat} |
| Description |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+ noformat} The problem can be reproduced with the following test case. {noformat} 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); {noformat} |
These two join queries use just two different forms for expressing the join operation and they are are equivalent:
{noformat} 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); {noformat} However their execution plans are different: {noformat} 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 | +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+ {noformat} The problem can be reproduced with the following test case. {noformat} 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); {noformat} |
| Fix Version/s | 5.5.57 [ 22539 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 80588 ] | MariaDB v4 [ 152078 ] |
The cause of this bug was the same as for the bug mdev-12670. So both of them were fixed by the same patch that was pushed into the 5.5 tree.