MySQL Forums
Forum List  »  Performance

slow Subquery
Posted by: Dominique Duay
Date: November 12, 2004 12:32AM

Hello,

I have the similar situation in a real life environment. Here is a test case to reproduce it:

2 Tables : Large (1 000 000 rows) and Small (10 rows).
CREATE TABLE SMALL (S INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(S)) TYPE INNODB;
CREATE TABLE LARGE (L INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(L)) TYPE INNODB;

How can I optimize the folowing query, without transforming it into a join and without changing the order between large and small (in that case it is quick):

SELECT *
FROM LARGE
WHERE EXISTS (SELECT *
FROM SMALL
WHERE S = L);

explain mysql> explain SELECT * FROM LARGE WHERE EXISTS (SELECT * FROM SMALL WHERE S = L);
+----+--------------------+-------+--------+---------------+---------+---------+--------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+---------+--------------------------+
| 1 | PRIMARY | LARGE | index | NULL | PRIMARY | 4 | NULL | 1311025 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | SMALL | eq_ref | PRIMARY | PRIMARY | 4 | optw.LARGE.L | 1 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+---------+--------------------------+
2 rows in set (0.00 sec)


I'm running 4.1.7, the table are optimized.

Cheers
Dominique

Options: ReplyQuote


Subject
Views
Written By
Posted
slow Subquery
3343
November 12, 2004 12:32AM
2144
November 12, 2004 12:55AM
2276
November 12, 2004 01:19AM
2391
November 13, 2004 12:00AM
2662
November 14, 2004 01:23AM


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.