MySQL optimizer rewrites uncorrelated subqueries (when the inner query does NOT use fields of the outer query) as correlated query, with perrformances of quadratic order rather than linear.
If the inner query does not use a WHERE with fields on the outer query, rewrite it as a JOIN
The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:
SELECT … FROM t1 WHERE t1.a IN (SELECT b FROM t2);
The optimizer rewrites the statement to a correlated subquery:
SELECT … FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.