I also tested your example query:
SELECT
`projects`.`id`,
`projects`.`title`,
`m_institutions`.`name` as 'managinginstitutionname',
`institutions`.`name`,
`contributions`.`year`,
`c_institution`.`name` as 'contributername',
`institutionyears`.`visitors` AS 'visitor_number',
`institutionyears`.`budget` AS 'operational_budget',
CASE `contributions`.`supporttype`
WHEN 1 THEN 'Financial'
WHEN 2 THEN 'Manhours'
END as 'supporttype' ,
CASE `contributions`.`fromoperationalbudget`
WHEN 0 THEN 'no'
WHEN 1 THEN 'yes'
ELSE 'unknown' END as 'fromoperationalbudget',
`contributions`.`amount`,
`contributions`.`currency`,
`contributions`.`manhours`,
`contributions`.`details`,
`projects`.`description`,
(SELECT GROUP_CONCAT(DISTINCT `categories`.`category` ORDER BY `categories`.`category` SEPARATOR ', ')
FROM `project_categories`,`categories`
WHERE `projects`.`id` = `project_categories`.`project_id` AND `project_categories`.`category_id` = `categories`.`id`) AS 'Eaza_conservation_categories' ,
(SELECT GROUP_CONCAT(DISTINCT `p_institutions`.`name` ORDER BY `p_institutions`.`name` SEPARATOR ', ' )
FROM `project_institutions`, `institutions` as `p_institutions`
WHERE `projects`.`id` = `project_institutions`.`project_id` AND `p_institutions`.`id` = `project_institutions`.`institution_id` ) AS 'Participating_institutions',
`subquery`.`species`,
(SELECT GROUP_CONCAT(DISTINCT `tags`.`tag` ORDER BY `tags`.`tag` SEPARATOR ', ')
FROM `project_tags`, `tags`
WHERE `projects`.`id` = `project_tags`.`project_id` AND `project_tags`.`tag_id` = `tags`.`id`) AS 'TAGs',
(SELECT GROUP_CONCAT(DISTINCT `animalgroups`.`animalgroup` ORDER BY `animalgroups`.`animalgroup` SEPARATOR ', ')
FROM `project_animalgroups`, `animalgroups`
WHERE `projects`.`id` = `project_animalgroups`.`project_id` AND `project_animalgroups`.`animalgroup_id` = `animalgroups`.`id` ) AS 'Animalgroups',
(SELECT GROUP_CONCAT(DISTINCT `habitats`.`habitat` ORDER BY `habitats`.`habitat` SEPARATOR ', ')
FROM `project_habitats`, `habitats`
WHERE `projects`.`id` = `project_habitats`.`project_id` AND `project_habitats`.`habitat_id` = `habitats`.`id` ) AS 'Habitats',
(SELECT GROUP_CONCAT(DISTINCT `countries`.`country` ORDER BY `countries`.`country` SEPARATOR ', ')
FROM `project_countries`, `countries`
WHERE `projects`.`id` = `project_countries`.`project_id` AND `project_countries`.`country` = `countries`.`code`) AS 'Countries',
(SELECT GROUP_CONCAT(DISTINCT `locations`.`location` ORDER BY `locations`.`location` SEPARATOR ', ')
FROM `project_locations`, `locations`
WHERE `projects`.`id` = `project_locations`.`project_id` AND `project_locations`.`location_id` = `locations`.`id` ) AS 'Locations',
(SELECT GROUP_CONCAT(DISTINCT threats.`threat` ORDER BY threats.`threat` SEPARATOR ', ')
FROM `project_threats`, `threats`
WHERE `projects`.`id` = `project_threats`.`project_id` AND `project_threats`.`threat_id` = `threats`.`id` ) AS 'Threats',
`projects`.`startdate`,
`projects`.`enddate`,
`projects`.`created_at`,
`projects`.`updated_at`
FROM `projects`
INNER JOIN `institutions` ON `projects`.`institution_id` = `institutions`.`id`
LEFT JOIN `institutions` as `m_institutions` ON `projects`.`managing_organisation_id` = `m_institutions`.`id`
LEFT JOIN `projectcontributions` as `contributions` ON `projects`.`id` = `contributions`.`project_id`
LEFT JOIN `institutions` as `c_institution` ON `c_institution`.`id` = `contributions`.`institution_id`
LEFT JOIN `institutionyears` ON `institutionyears`.`institution_id` = `c_institution`.`id`
LEFT JOIN `project_institutions` ON `projects`.`id` = `project_institutions`.`project_id`
LEFT JOIN `institutions` as `p_institutions` ON `p_institutions`.`id` = `project_institutions`.`institution_id`
LEFT OUTER
JOIN ( SELECT `project_taxonomy`.`project_id`
, GROUP_CONCAT(DISTINCT `taxonomy`.`commonname`
ORDER BY `taxonomy`.`commonname`
SEPARATOR ', ') AS species
FROM `project_taxonomy`
INNER
JOIN `taxonomy`
ON `taxonomy`.`id` = `project_taxonomy`.`taxonomy_id`
GROUP
BY `project_taxonomy`.`project_id`
) AS subquery
ON `subquery`.`project_id` = `projects`.`id`
GROUP BY `projects`.`id`, `contributions`.`id`
ORDER BY `projects`.`id` ASC, `contributions`.`year` ASC
Which gives me the same amount of rows returned (8217) in 2,09 sec. Compared to
SELECT
`projects`.`id`,
`projects`.`title`,
`m_institutions`.`name` as 'managinginstitutionname',
`institutions`.`name`,
`contributions`.`year`,
`c_institution`.`name` as 'contributername',
`institutionyears`.`visitors` AS 'visitor_number',
`institutionyears`.`budget` AS 'operational_budget',
CASE `contributions`.`supporttype`
WHEN 1 THEN 'Financial'
WHEN 2 THEN 'Manhours'
END as 'supporttype' ,
CASE `contributions`.`fromoperationalbudget`
WHEN 0 THEN 'no'
WHEN 1 THEN 'yes'
ELSE 'unknown' END as 'fromoperationalbudget',
`contributions`.`amount`,
`contributions`.`currency`,
`contributions`.`manhours`,
`contributions`.`details`,
`projects`.`description`,
(SELECT GROUP_CONCAT(DISTINCT `categories`.`category` ORDER BY `categories`.`category` SEPARATOR ', ')
FROM `project_categories`,`categories`
WHERE `projects`.`id` = `project_categories`.`project_id` AND `project_categories`.`category_id` = `categories`.`id`) AS 'Eaza_conservation_categories' ,
(SELECT GROUP_CONCAT(DISTINCT `p_institutions`.`name` ORDER BY `p_institutions`.`name` SEPARATOR ', ' )
FROM `project_institutions`, `institutions` as `p_institutions`
WHERE `projects`.`id` = `project_institutions`.`project_id` AND `p_institutions`.`id` = `project_institutions`.`institution_id` ) AS 'Participating_institutions',
(SELECT GROUP_CONCAT(DISTINCT `taxonomy`.`commonname` ORDER BY `taxonomy`.`commonname` SEPARATOR ', ')
FROM `project_taxonomy`, `taxonomy`
WHERE `projects`.`id` = `project_taxonomy`.`project_id` AND `project_taxonomy`.`taxonomy_id` = `taxonomy`.`id` ) AS 'Species',
(SELECT GROUP_CONCAT(DISTINCT `tags`.`tag` ORDER BY `tags`.`tag` SEPARATOR ', ')
FROM `project_tags`, `tags`
WHERE `projects`.`id` = `project_tags`.`project_id` AND `project_tags`.`tag_id` = `tags`.`id`) AS 'TAGs',
(SELECT GROUP_CONCAT(DISTINCT `animalgroups`.`animalgroup` ORDER BY `animalgroups`.`animalgroup` SEPARATOR ', ')
FROM `project_animalgroups`, `animalgroups`
WHERE `projects`.`id` = `project_animalgroups`.`project_id` AND `project_animalgroups`.`animalgroup_id` = `animalgroups`.`id` ) AS 'Animalgroups',
(SELECT GROUP_CONCAT(DISTINCT `habitats`.`habitat` ORDER BY `habitats`.`habitat` SEPARATOR ', ')
FROM `project_habitats`, `habitats`
WHERE `projects`.`id` = `project_habitats`.`project_id` AND `project_habitats`.`habitat_id` = `habitats`.`id` ) AS 'Habitats',
(SELECT GROUP_CONCAT(DISTINCT `countries`.`country` ORDER BY `countries`.`country` SEPARATOR ', ')
FROM `project_countries`, `countries`
WHERE `projects`.`id` = `project_countries`.`project_id` AND `project_countries`.`country` = `countries`.`code`) AS 'Countries',
(SELECT GROUP_CONCAT(DISTINCT `locations`.`location` ORDER BY `locations`.`location` SEPARATOR ', ')
FROM `project_locations`, `locations`
WHERE `projects`.`id` = `project_locations`.`project_id` AND `project_locations`.`location_id` = `locations`.`id` ) AS 'Locations',
(SELECT GROUP_CONCAT(DISTINCT threats.`threat` ORDER BY threats.`threat` SEPARATOR ', ')
FROM `project_threats`, `threats`
WHERE `projects`.`id` = `project_threats`.`project_id` AND `project_threats`.`threat_id` = `threats`.`id` ) AS 'Threats',
`projects`.`startdate`,
`projects`.`enddate`,
`projects`.`created_at`,
`projects`.`updated_at`
FROM `projects`
INNER JOIN `institutions` ON `projects`.`institution_id` = `institutions`.`id`
LEFT JOIN `institutions` as `m_institutions` ON `projects`.`managing_organisation_id` = `m_institutions`.`id`
LEFT JOIN `projectcontributions` as `contributions` ON `projects`.`id` = `contributions`.`project_id`
LEFT JOIN `institutions` as `c_institution` ON `c_institution`.`id` = `contributions`.`institution_id`
LEFT JOIN `institutionyears` ON `institutionyears`.`institution_id` = `c_institution`.`id`
LEFT JOIN `project_institutions` ON `projects`.`id` = `project_institutions`.`project_id`
LEFT JOIN `institutions` as `p_institutions` ON `p_institutions`.`id` = `project_institutions`.`institution_id`
GROUP BY `projects`.`id`, `contributions`.`id`
ORDER BY `projects`.`id` ASC, `contributions`.`year` ASC
Which also returns 8217 rows, in 2,09 sec