MYSQL WHERE-IN Subquery Runs Forever

Question

I have a MySQL table. Let's call it Widgets. The Widget table has 3 fields: id, type_id, and name. I want, in one query, to get all the widgets that share a type_id with the Widget named 'doodad'. I've written 2 queries:

1. Give me the type_id of the widget with the name 'doodad'.
2. Give me all widgets with that type_id.

This works. Each query, independently achieves its goal.

But when I combine them into a single nested query, it runs forever, infinite loop style. It looks like this:

SELECT * FROM widgets WHERE type_id IN  (
    SELECT type_id FROM widgets WHERE name = 'doodad'
);

Can anyone explain this? Is it because I am writing a nested query which is operating on the same table twice?

Little wheel, why spinnest thou?

Answer 1

Using a JOIN risks duplicating results - an EXISTS will work similar to an IN, without the duplication risk:

SELECT x.* 
  FROM widgets x
 WHERE EXISTS (SELECT NULL
                 FROM WIDGETS y
                WHERE y.name = 'doodah'
                  AND y.type_id = x.type_id)

Answer 2

There is an issue in MySQL and in where even uncorrelated subqueries are treated as though they were correlated and re-evaluated for each row.

In the explain plan the select type will likely be showing as dependant subquery rather than just subquery as would be desired.

I suggest trying the approach described at the end of this article of using a derived table to materialize the inner result set.

Or alternatively you could look at the constify procedure here to see if it will assist you in getting around this issue.