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