Bug #24972 Submitted: Join and sub-query execution differences 11 Dec 2006 21:55 5 Sep 2012 16:35 Jim Bullington Closed None MySQL Server: Optimizer S5 (Performance) 5.0.27, 6.0.14 Any (Windows) Any Triaged: D2 (Serious)

[11 Dec 2006 21:55] Jim Bullington
```Description:
There appears to be a large differential between a join query and a sub-query that produce equivalent results:

t1 has 1000 records, t2 has 100000 records

select b
from t1
join t2 on t2.c = t1.a
where d = 1000
order by b;

Completes in 0.1 seconds

However,

select b
from t1
where a in (select c from t2 where d = 1000)
order by b;

Completes in 44 seconds

Should there be such a large performance difference between these 2 equivalent queries?

How to repeat:
create table t1 (
a int,
b int,
primary key(a)
);

delimiter \$

create procedure build_t1(p1 int)
begin
set @x = 0;
repeat
set @x = @x + 1;
insert into t1 values(@x, floor(1+(rand()*10000)));
until @x > p1
end repeat;
end \$

delimiter ;

call build_t1(1000);

create table t2 (
a int,
c int,
d int,
primary key(a)
);

delimiter \$

create procedure build_t2(p1 int)
begin
set @x = 0;
repeat
set @x = @x + 1;
insert into t2 values(@x, floor(1+(rand()*1000)), floor(1+(rand()*10000)));
until @x > p1
end repeat;
end \$

delimiter ;

call build_t2(100000);

select b
from t1
join t2 on t2.c = t1.a
where d = 1000
order by b;

select b
from t1
where a in (select c from t2 where d = 1000)
order by b;```
[11 Dec 2006 22:45] Miguel Solorzano
`Thank you for the bug report. This is a well known problem with sub-queries. Will be fixed in MySQL 5.2 (work in progress already).`
[13 Dec 2006 21:11] Jim Bullington
`My goodness!!  5.2 is a long ways off!  Does the commercial MySQL version suffer from the same problem?`
[30 Nov 2009 19:28] Valeriy Kravchuk
```The problem is, actually, even worse with 6.0.14:

mysql> select b
-> from t1
-> where a in (select c from t2 where d = 1000)
-> order by b;

+------+
| b    |
+------+
| 1517 |
| 2187 |
| 2417 |
| 2556 |
| 2644 |
| 2796 |
| 3106 |
| 3220 |
| 3785 |
| 4782 |
| 5975 |
| 8733 |
+------+
12 rows in set (8 min 11.35 sec)

mysql> explain select b
-> from t1
-> where a in (select c from t2 where d = 1000)
-> order by b;
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL |   1001 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2    | ALL  | NULL          | NULL | NULL    | NULL | 100001 | Using where; FirstMatch(t1)     |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 6.0.14-alpha-debug |
+--------------------+
1 row in set (0.00 sec)

mysql> explain select b
-> from t1
-> join t2 on t2.c = t1.a
-> where d = 1000
-> order by b;
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+----------------------------------------------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL      | 100001 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.c |      1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+-----------+--------+----------------------------------------------+
2 rows in set (0.01 sec)

mysql> select b from t1 join t2 on t2.c = t1.a where d = 1000 order by b;
+------+
| b    |
+------+
| 1517 |
| 2187 |
| 2417 |
| 2556 |
| 2644 |
| 2796 |
| 3106 |
| 3220 |
| 3785 |
| 4782 |
| 5975 |
| 8733 |
+------+
12 rows in set (0.07 sec)```
[5 Sep 2012 16:35] Paul Dubois
`Fixed with subquery optimizations in 5.6.5.`