slow Subquery

[Dominique Duay]

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