Subqueries vs joins

Question

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically.

But, yeah, explain-plan will give you the dirty details.

You are running the subquery once for every row whereas the join happens on indexes.

Here's an example of how subqueries are evaluated in MySQL 6.0.

The new optimizer will convert this kind of subqueries into joins.

Run the explain-plan on each version, it will tell you why.

before the queries are run against the dataset they are put through a query optimizer, the optimizer attempts to organize the query in such a fashion that it can remove as many tuples (rows) from the result set as quickly as it can. Often when you use subqueries (especially bad ones) the tuples can't be pruned out of the result set until the outer query starts to run.

With out seeing the the query its hard to say what was so bad about the original, but my guess would be it was something that the optimizer just couldn't make much better. Running 'explain' will show you the optimizers method for retrieving the data.

Look at the query plan for each query.

Where in and Join can typically be implemented using the same execution plan, so typically there is zero speed-up from changing between them.