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 [URL=“http://Common MySQL Queries”]Common MySQL Queries
Now my question would be, whether “IN (subquery)” is ever fit to optimize a query, and if so, how?
Tobias
| [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.