You have
Please use aliases and tag each field so we know which field comes from which table:
SELECT lga_name,lga_code,lga.postcode,
s.ratio,
COUNT(DISTINCT s.participant_id),
COUNT(DISTINCT s.participant_id)*lga.ratio AS postcode_total,
COUNT(DISTINCT CASE WHEN s.`year` = 2010 THEN s.participant_id END)*lga.ratio as postcode_total_start_year,
COUNT(DISTINCT CASE WHEN s.`year` = 2013 THEN s.participant_id END)*lga.ratio as postcode_total_end_year
FROM ssa_participant AS s
LEFT JOIN abs_postcode_lga AS lga ON lga.postcode = s.postcode
WHERE s.sport_id_fk = 18
AND s.`year` >= 2010
AND s.`year` <= 2013
AND lga.postcode IS NOT NULL
GROUP BY lga_name,lga_code,lga.postcode, lga.ratio
Remove the "LEFT" and "AND lga.postcode IS NOT NULL" since a plain JOIN means just that.
Add a compound index that matches the WHERE clause:
INDEX(sport_id_fk, `year`)
(and DROP the index on sport_id_fk because it will not longer be useful.)
Add a PRIMARY KEY to ssa_participant. Is there some 'natural' combination of fields that is UNIQUE?
Use the YEAR datatype, not VARCHAR.
`age` should be computed, not stored. (assuming it changes daily!)
How many distinct values are there for `year`? Probably so few that the index on that field will never be used; DROP it.
What version of MySQL are you using?
What is the value of innodb_buffer_pool_size? How much RAM do you have?