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));
The 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);
The 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.