GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #2
    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.

    Comment

    Working...
    X