MySQL Forums
Forum List  »  Optimizer & Parser

Extremely slow subqueries when used in joins
Posted by: kalinda
Date: March 08, 2007 06:50AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Extremely slow subqueries when used in joins
2725
March 08, 2007 06:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.