MySQL Query Select using sub-select takes too long
- by True Soft
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.
