The Slowness of "WHERE something IN (SELECT....)"

[tobias74]

Hello!

When discussing performance issues on inner-join-queries, people tend to advise using a subquery like “SELECT id FROM table WHERE value IN (SELECT something FROM othertable WHERE original_condition)”.

As this strategy did not work for me at all, I kept looking for some solution and came across an article which can be found at http://www.artfulsoftware.com/infotree/queries.php#568

Now my question would be, whether “IN (subquery)” is ever fit to optimize a query, and if so, how?

Tobias

[Hubert_Roksor]

[B]tobias74 wrote on Tue, 15 April 2008 16:55[/B]

people tend to advise using a subquery

Hmm, not MySQL people I'm afraid. MySQL is known to execute such subqueries quite innefficiently, that's why those should be rewritten as an INNER JOIN whenever possible.

[B]tobias74 wrote on Tue, 15 April 2008 16:55[/B]

Now my question would be, whether "IN (*subquery*)" is ever fit to optimize a query, and if so, how?

I can't think of any case where a subquery in the IN clause would be more efficient than a JOIN or a derived table (subquery in the FROM clause, also called materialization). If there's one, it must be a tricky edge case.

Note that starting with 6.0, most of subqueries in a IN clause will be transformed automatically and executed as one of the other forms described above (semi-join, materialization). You’ll find more info about that in this MySQL Forge article.