Poor UNION ALL performance in MySQL
<p>I have a database with rows like the following:</p>

<pre><code>+------------+---------+------------+-------+
| continent  | country | city       | value |
+------------+---------+------------+-------+
| Asia       | China   | Beijing    | 3     |
| ...        | ...     | ...        | ...   |
| N. America | USA     | D.C        | 7     |
| ....       | ....    | ....       | ....  |
</code></pre>

<p>In order to generate a treemap visualization, I need to work this into a table with the following shape:</p>

<pre><code>+-----+------------+-------+
| uid | parent-uid | value |
+-----+------------+-------+
</code></pre>

<p>In this case, <code>Asia</code> is the "parent" for <code>China</code>, which is the "parent" for <code>Beijing</code>. So for those three you'd have <strong>something</strong> like:</p>

<pre><code>+---------+--------+-----+
| Beijing | China  | 3   |
| China   | Asia   | ... |
| Asia    | global | ... |
+---------+--------+-----+
</code></pre>

<p>The "value" for <code>China</code> needs to be an aggregate of all child values. Similarly the value of <code>Asia</code> needs to be an aggregate of all child values.</p>

<p>To accomplish this purely in SQL I created the following three queries and combined them with <code>UNION ALL</code>:</p>

<pre><code># City-level:
SELECT
     CONCAT(continent, "-", country, "-", city) as uid,
     CONCAT(continent, "-", country) as parentuid,
     value
FROM
     table

UNION ALL

# Country-level
SELECT
     CONCAT(continent, "-", country) as uid,
     continent as parentuid,
     SUM(value) as value
FROM
     table
GROUP BY
     country

UNION ALL

# Continent-level
SELECT
    continent as uid,
    "global" as parentuid,
    SUM(value) as value
FROM
    table
GROUP BY
    continent
</code></pre>

<p>Each of the individual queries completes in milliseconds. City-level, country-level, and continent-level all return results in &lt; 0.01 seconds</p>

<p>When I union them all together it suddenly takes 8 seconds to get results!</p>

<p>I've tried Googling the issues but everything just says "Use <code>UNION ALL</code> instead of <code>UNION</code>" (I already am)</p>

<p>I considered that it might not have enough RAM to build the temporary results table so it's disk trashing, but I don't know how to increase the memory limit. I tried bumping <code>innodb_buffer_pool_size</code> to 1GB (1073741824) but it didn't help</p>