| The Slowness of "WHERE something IN (SELECT....)" [message #2959] |
Tue, 15 April 2008 10:55  |
 tobias74
Messages: 4
Registered: April 2008 |
Junior Member |
|
|
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
|
|
|
| Re: The Slowness of "WHERE something IN (SELECT....)" [message #2960 is a reply to message #2959 ] |
Tue, 15 April 2008 12:57  |
Hubert Roksor
Messages: 3
Registered: April 2008 |
Junior Member |
|
|
| tobias74 wrote on Tue, 15 April 2008 16:55 |
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.
| tobias74 wrote on Tue, 15 April 2008 16:55 |
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.
|
|
|
Members
Search
Help
Register
Login
Home
