MySQL Query Select using sub-select takes too long

Question

I noticed something strange while executing a select from 2 tables:

SELECT * FROM table_1 WHERE id IN (
    SELECT id_element FROM table_2 WHERE column_2=3103);

This query took approximatively 242 seconds.

But when I executed the subquery

SELECT id_element FROM table_2 WHERE column_2=3103

it took less than 0.002s (and resulted 2 rows).
Then, when I did

SELECT * FROM table_1 WHERE id IN (/* prev.result */)

it was the same: 0.002s.

I was wondering why MySQL is doing the first query like that, taking much more time than the last 2 queries separately? Is it an optimal solution for selecting something based from the results of a sub-query?

Other details: table_1 has approx. 9000 rows, and table_2 has 90000 rows.

After I added an index on column_2 from table_2, the first query took 0.15s.

Answer 1

Perhaps the query analyzer evaluates the subquery for every row.

Try replacing the subquery with an INNER JOIN, and see if that improves performance:

SELECT     * 
FROM       table_1 t1
INNER JOIN table_2 t2
ON         t1.id = t2.id_element
           AND t2.column_2 = 3103

Answer 2

This is a known bug in mysql prior to ver 6.

Work around I have found is:

SELECT * FROM table_1 WHERE id IN ( SELECT id_element FROM (SELECT id_element FROM table_2 WHERE column_2=3103) as q)

Answer 3

I have the same problem. I added an INDEX for the tables (guess you already have) and used the USE INDEX directive. In your case it should look like this:

SELECT * FROM table_1 USE INDEX(id)
WHERE id IN (SELECT id_element FROM table_2 WHERE column_2=3103);

To me it made things better.