Extremely slow subqueries when used in joins

[kalinda]

Hi all,

I have a problem with subqueries. The following setup:

create table t1(a int primary key);
create table t2(b int primary key);
<insert some data>

yields this result for the following query, which is optimized as expected:

mysql> explain select t1.a from t1 inner join t2 as t2 on t1.a = t2.b;
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------------+
| 1 | SIMPLE | t2 | index | PRIMARY | PRIMARY | 4 | NULL | 2 | Using index |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | apa.t2.b | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------------+
2 rows in set (0.00 sec)


But I need to nest queries:

mysql> explain select t1.a from t1 inner join (select * from t2) as t2 on t1.a = t2.b;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DERIVED | t2 | index | NULL | PRIMARY | 4 | NULL | 3 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
3 rows in set (0.00 sec)


It seems like this creates a full table scan in the subquery result for each comparison in the primary select. Of course, this is extremely slow and not really usable.

I want to generate queries, which is much easier with subqueries, so I wonder if anyone knows if this is possible in some way?
I have tried with the latest 5.2-falcon bitkeeper version since it has been hinted that some subquery-related problems shall be resolved in this release, but with the same result.