Using result set from an outer query in inner query in MySQL

Question

I have two tables called "nodes" and "links". I am performing the following query and it returns the results I want. How to optimize this one? I am using the resultset of the outer query in inner query. How to reference to the resultset of the outer query in inner queries so as to prevent running the same outer query again. Repeated queries between arrows.

SELECT * FROM nodes where name = 'Compost' union all 
-> SELECT * FROM nodes where id in
 ( SELECT target from links where source in 
    ( SELECT id FROM  (SELECT * FROM nodes where name = 'Compost' <- ) as a)) union all  SELECT * FROM nodes where id in 
        ( SELECT target from links where source in (SELECT id FROM (   -> SELECT * FROM nodes where id in ( SELECT target from links where source in 
            ( SELECT id FROM (SELECT * FROM nodes where name = 'Compost'<- ) as c))) as b))

score 0 · Answer 1 · answered Mar 07 '15 at 02:22

Turn IN ( SELECT ... ), which performs poorly, into JOIN.

SELECT  ...
    FROM  a
    WHERE  x IN (
        SELECT  x
            FROM  b
            WHERE  test_b 
                )
      AND  test_a;

-->

SELECT  ...
    FROM  a
    JOIN  b USING(x)
    WHERE  test_a
      AND  test_b;

And see that you have INDEX(x) on at least one of the tables.

Using result set from an outer query in inner query in MySQL

1 Answers1