For the last couple of days, I've been thinking about the best way to optimize the existing D query, which is a bit slow, and also to enable us to do the other two calculations we need to do for our combined ethnicities without making the whole show painfully slow. I now have a solution, using temporary tables.

The SQL block below will do the following:

1. Create a temporary table for one city/year, which is deleted when it goes out of scope (i.e. when the current connection is terminated).
2. Populate the table by creating two constructed ethnicities, one by combining ethnicities 23 and 24, and the other combining 35 and 36.
3. For each combined ethnicity, in each of the specified tracts, create a new row which sums the o_tot values in the original table.

The effect of this is to give us a new table in which the new, constructed ethnicities are represented in sum, as if they were original ethnicities. This table is analogous to any of the original tables -- it has the same structure -- and therefore JD's original, slick and fast SQL can be run against this table to do the three calculations we need to do. The value of o in the code below is negative, to distinguish it from any of the original, genuine ethnicity id codes. It would also be straightforward to add a new column in the constructed table which contains the user-supplied name for the constructed ethnicity, making it easy to retrieve in the subsequent query.

I now have to take this code forward by first using this approach to create the existing D table, alongside the one created by my current code; ensure that the values resulting from this approach are the same as the old ones; and then test each approach to confirm what I'm confident will be the case, that the new approach is significantly faster.

This is the SQL:

CREATE TEMPORARY TABLE `test` (
SELECT SUM( `o_tot` ) AS `o_tot` , "-10" AS `o` , `ct` , `ct_tot`
FROM `ct_1961_205_sc`
WHERE (`o` IN ("22", "23"))
AND `ct` IN ("1.000", "2.000")
GROUP BY `ct`
)
UNION 
(
SELECT SUM( `o_tot` ) AS `o_tot` , "-20" AS `o` , `ct` , `ct_tot`
FROM `ct_1961_205_sc`
WHERE (`o` IN ("35", "36"))
AND `ct` IN ("1.000", "2.000")
GROUP BY `ct`
);
SELECT * FROM `test`;

The last bit, run in phpMyAdmin, shows the temporary table contents.