Unexpected extremely long query time (~5 minutes using nested WHEN-INs) For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes). 

The data in the database is highly (and not arbitrarily) normalized. It very efficient at organizing and shuffling data to be displayed in a lot of very helpful ways for different purposes, except this one particular query is throwing a wrench into it. 

I can't understand the reason for it. However, some background information that may shed some light onto otherwise arbitrarily convoluted queries.



The company has divided the world up into many many teams (macroregions). Everybody belongs to one or two teams, based on their expertise.


For example, there are many diverse teams. A few examples are Spanish, Sahara, Iberia, Portuguese, Jungle teams. Each of the teams has considerable overlap with other teams, but is in some senses independent.
The Arabic team works quite closely with the Sahara team, by virtue of the fact that the database tells them they have to work together on certain assignments, because of overlapping geographic locations. The Spanish and the Portuguese team also work closely together, they both work with the Americas and Europe teams and the Portuguese team also works with the Africa team, as does the Arabic team.
Each team has a given set of regions, which are also not unique to that particular team. For example, the Mediterranean region belongs to around 12 teams, and when an event occurs there, they all work on it together.
Each country belongs to one or more regions. Turkey belongs to Central Asia, Europe and even Mediterranean, and a few others.


Given all that, it is necessary to show each individual what other people on their teams are doing, as well as people who are not on their team, but have overlapping regions.

Query 1 accomplishes this perfectly, and very quickly less than .09 seconds.



                SELECT report_name 
                FROM reports 
                WHERE region IN (
                    SELECT distinct region 
                    FROM macroregions
                    WHERE macroregion IN (
                        SELECT distinct macroregion
                        FROM users 
                        WHERE callsign = '$thisuser'
                    )
                ) 


It is also important people can see every occurrence of the report_name, when it Query 1 deems that the individual logged in should know about it. Reports_names are automatically generated and reused for each geographic location


If I am on the East Asia team and someone in my area is working with the Spanish team, there would be two entries in the database:


20120210JOMX01 Japan Okinawa
20120210JOMX01 Mexico Nuevo Leon



Query 2 takes Query 1 and wraps one layer of WHEN-INs around it. It allows the East Asia team members know that someone on their team is working in Mexico. But the performance delay is unacceptable and totally unusable; it takes nearly 5 minutes to complete a single query!



        SELECT * 
        FROM reports
        WHERE report_name IN (
            SELECT report_name 
            FROM reports 
            WHERE region IN (
                SELECT distinct region 
                FROM macroregions
                WHERE macroregion IN (
                    SELECT distinct macroregion
                    FROM users 
                    WHERE callsign = '$thisuser'
                )
            )    
        )




Despite the fact that the query works, it takes too long to be effective. Again, it is startling how long this particular query takes. Whereas nothing else in the interface has given me any sort of performance issue (i.e. they all take less than one second).

What steps can I take to fix this?
First step: get rid of subqueries.  Convert them to proper joins, or co-related subqueries.