MySQL - "select" query inside IN (...) optimization issue (=> Hierarquical query )

Before you start down voting and deny me an answer, please note that I am a complete beginner :). I already searched for an answer but it seems to be quite specific.

(how I understand it as a programmer, if the select statements where for loops, it would be like a loop inside a loop inside a loop :D)

The question is about Optimizing my sql query because it takes a few seconds to fetch the data.

The SQL Broken down:

1st. query

SELECT * 
FROM r_submenuitems 
WHERE modifier1 IN (SELECT submenu_id FROM r_submenuitems WHERE item_id = 1068)

this query gets a list (the list in the 2nd query to be exact), and I want to go one step higher. I.e. use this list in the IN clause of the query once again and fetch the new list.

2nd. query

SELECT submenu_id 
FROM r_submenuitems 
WHERE modifier1 IN (31050, 131050,3912, 103122, 103165, 7772, 7782)

To merge the 2 querys into 1, I did the following:

SELECT submenu_id FROM r_submenuitems 
WHERE modifier1 IN (SELECT submenu_id FROM r_submenuitems 
    WHERE modifier1 IN (SELECT submenu_id FROM r_submenuitems 
        WHERE item_id = 1068))

Which takes ages to fetch the data.

Is there a better (quicker to fetch) way to merge the two querys above than this?? If not, is the first solution, ie having 2 querys better than having the all-in-one query??

edit: The first query returns a list of entries (31050, 131050,3912, 103122, 103165, 7772, 7782). Which I then proceed to feed into the 2nd query. The all-in-one query attempts to merge this two into one, i.e. go even deeper Check comments of approved answer, turns out what I was looking for is called Hierarquical query.

Solution

Try

SELECT submenu_id  
  FROM r_submenuitems r1
           INNER JOIN r_submenuitems r2
             ON r1.modifier1 = r2.submenu_id
 WHERE r2.item_id = 1068

Your query is fetching your table three times and that is why it is taking so long. And one of then is completelly unnecessary, you are just repeating the code.

SELECT submenu_id FROM r_submenuitems 
 WHERE modifier1 IN (SELECT submenu_id                   --All this IN statement is
                       FROM r_submenuitems               --unnecessary you are already
                      WHERE modifier1 IN ( SELECT submenu_id -- doing on the inner IN
                                             FROM r_submenuitems 
                                            WHERE item_id = 1068
                                         )
                    )

Edit 2

As discussed on the comments you are looking for a Hierarquical Query, since mysql doesn't support this kind of operation on native commands you have at least to know how deep you want to go to create a query to fetch the data from that level, you will see that it needs to add a JOIN operation for every level.

The alternative to that is to create a store procedure with some recursion (I will not explain this) take a look here MANAGING HIERARCHICAL DATA IN MYSQL.

The query for your problem would be (for the third level)

SELECT r1.submenu_id  
  FROM r_submenuitems r1 
        INNER JOIN r_submenuitems r2 ON r1.modifier1 = r2.submenu_id
        INNER JOIN r_submenuitems r3 ON r2.modifier1 = r3.submenu_id
 WHERE r3.item_id = 1068