I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design.
One of the tables looks as follows (relevant cols only):
CREATE TABLE IF NOT EXISTS CountryData ( countryDataID INT PRIMARY KEY AUTO_INCREMENT, dataID INT NOT NULL REFERENCES DataSources (dataID), dataCode VARCHAR(30) NULL, countryID INT NOT NULL REFERENCES Countries (countryID), year INT NOT NULL , data DEC(20,4) NULL, INDEX countryDataYear (dataID, countryID, year));
data column has values for a few hundred indicators, 90 countries, and 30 years for ~1mn rows total. A standard query requires selecting N indicators for a particular year and C countries, yielding a CxN table for 90 rows max.
With all values in a single column, self-joins seemed like the way to go. So I have experimented with various suggestions to speed those up, including indexing and creating new (temp) tables. At 9 self-joins, the query takes a little under 1 min. Beyond that, it spins forever.
The new table from where the self-joins take place has only about 1,000 rows, indexed on what seem to be the relevant variables - creation takes about 0.5 sec:
CREATE TABLE Growth SELECT dataID, countryID, year, data FROM CountryData WHERE dataID > 522 AND year = 2017; CREATE INDEX growth_ix ON Growth (dataID, countryID);
SELECT query then arranges up to XX indicators in the results table, with XX unfortunately <10:
SELECT Countries.countryName AS Country, em01.em, em02.em, em03.em ... emX.em FROM (SELECT em1.data AS em, em1.countryID FROM Growth AS em1 WHERE em1.dataID = 523) as em01 JOIN (SELECT em2.data AS em, em2.countryID FROM Growth AS em2 WHERE em2.dataID = 524) as em02 USING (countryID) JOIN (SELECT em3.data AS em, em3.countryID FROM Growth AS em3 WHERE em3.dataID = 525) as em03 USING (countryID) ... JOIN (SELECT emX.data AS em, emX.countryID FROM Growth AS em5 WHERE emX.dataID = 527) as emXX USING (countryID) JOIN Countries USING (countryID)
I'd actually like to retrieve a few more variables, plus potentially join other tables. Now I'm wondering whether there's a way to run this more efficiently, or whether I should take an altogether different approach, such as using wide tables with indicators in different columns to avoid self-joins.