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

Question

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.

Jorge Campos · Accepted Answer · 2015-01-14T12:19:45.460

3

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

edited Jan 14 '15 at 12:19

answered Jan 14 '15 at 11:52

Jorge Campos

22,647
7
56
87

indeed I am BUT, the data it fetches are different, your suggestion brings the first list, so its the same as my first query. I guess its the way the data are stored. It has a parent which has a grand-parent which has a grand-grand-parent, and I want the grand-grand-parent list :D – hahaha Jan 14 '15 at 12:00
There is no difference from the first and the second query. Only that the second you are fetching only one field `submenu_id` which can be added easelly on my query. Unless you mix the names of the tables on your question is all the same. – Jorge Campos Jan 14 '15 at 12:02
hmm maybe I wasnt clear in my question, Ill edit it and you let me know it it is any clearer :) – hahaha Jan 14 '15 at 12:02
Now I understand. You are looking for a Hierarquical query. Edit your question to be clear on this point, also add sample data of your table just with the columns that matter and your desired result. – Jorge Campos Jan 14 '15 at 12:04
hm its really hard to write it because there is no table thingy =[ it might take a while or if you prefer i could add a screenshot – hahaha Jan 14 '15 at 12:21
thank you it is perfect :). Ill edit my title to include Hierarquical query – hahaha Jan 14 '15 at 12:26
Glad that I could help. – Jorge Campos Jan 14 '15 at 12:28

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

1 Answers1