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
Jungleteams. Each of the teams has considerable overlap with other teams, but is in some senses independent.
Arabicteam works quite closely with the
Saharateam, by virtue of the fact that the database tells them they have to work together on certain assignments, because of overlapping geographic locations. The
Portugueseteam also work closely together, they both work with the
Europeteams and the
Portugueseteam also works with the
Africateam, as does the
Each team has a given set of regions, which are also not unique to that particular team. For example, the
Mediterraneanregion 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.
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 Asiateam and someone in my area is working with the
Spanishteam, there would be two entries in the database:
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?