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

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.

Answer 2

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)