Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL WHERE-IN Subquery Runs Forever

Tags:

mysql

nested

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?

like image 742
Whit Avatar asked Aug 29 '10 23:08

Whit


1 Answers

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.

like image 62
Martin Smith Avatar answered Oct 18 '22 02:10

Martin Smith