Creating Sort Index takes 96% of query time even without any ORDER BY I have this query:

SELECT itr.recipe_id,
   SUM(itr.weight),
   SUM(aprice_weight),
   SUM(itr.weight+aprice_weight) AS score
FROM
  (SELECT itfin.id AS flyer_item_id,
          itfin.flyer_id,
          MAX(itfin.max_weight) AS aprice_weight,
          itfin.ingredient_id
   FROM
     (SELECT MAX(price_weight) AS max_weight,
             flyer_items.id,
             flyer_items.flyer_id,
             ingredient_to_flyer_item.ingredient_id
      FROM flyer_items
      JOIN ingredient_to_flyer_item ON flyer_items.id = ingredient_to_flyer_item.flyer_item_id
      WHERE flyer_items.flyer_id IN (2)
      GROUP BY ingredient_to_flyer_item.flyer_item_id) AS itfin
   JOIN ingredient_to_flyer_item ON itfin.id = ingredient_to_flyer_item.flyer_item_id
   GROUP BY itfin.ingredient_id) AS itf
INNER JOIN `ingredient_to_recipe` AS `itr` ON `itf`.`ingredient_id` = `itr`.`ingredient_id`
GROUP BY `itr`.`recipe_id`
ORDER BY `score` DESC
LIMIT 12


But even without the ORDER BY clause, execution time is still the same, around 350ms. I tried looking at the indexes to see what I can do, but nothing worked so far. Not even putting a bigger value for SORT_BUFFER_SIZE in MySQL settings.

The profiler tells me that a single Create Sort Index (out of 3) takes 96% of the query time.

Here is the EXPLAIN result:

| id | select_type | table                    | partitions | type   | possible_keys           | key           | key_len | ref                      | rows | filtered | extra                                        |
|----|-------------|--------------------------|------------|--------|-------------------------|---------------|---------|--------------------------|------|----------|----------------------------------------------|
| 1  | PRIMARY     | &lt;derived2&gt;               | NULL       | ALL    | NULL                    | NULL          | NULL    | NULL                     | 583  | 100.00   | Using temporary; Using filesort              |
| 1  | PRIMARY     | itr                      | NULL       | ref    | recipe_id,ingredient_id | ingredient_id | 4       | itf.ingredient_id        | 35   | 100.00   | NULL                                         |
| 1  | PRIMARY     | r                        | NULL       | eq_ref | id                      | id            | 4       | metadata2.itr.recipe_id  | 1    | 10.00    | Using where                                  |
| 2  | DERIVED     | &lt;derived3&gt;               | NULL       | ALL    | NULL                    | NULL          | NULL    | NULL                     | 246  | 100.00   | Using temporary; Using filesort              |
| 2  | DERIVED     | ingredient_to_flyer_item | NULL       | ref    | flyer_item_id           | flyer_item_id | 4       | itfin.id                 | 2    | 100.00   | Using index                                  |
| 3  | DERIVED     | flyer_items              | NULL       | ALL    | id_2,id,flyer_id        | NULL          | NULL    | NULL                     | 104  | 100.00   | Using where; Using temporary; Using filesort |
| 3  | DERIVED     | ingredient_to_flyer_item | NULL       | ref    | flyer_item_id           | flyer_item_id | 4       | metadata2.flyer_items.id | 2    | 100.00   | NULL                                         |


Any help to speed this query up would be so appreciated. Thank you!

UPDATE

Link to .sql demo tables: https://nofile.io/f/F4YSEu8DWmT/meta.zip
Each row in the EXPLAIN's output having NULL in the 'key' column should be investigated. Those row marked as (derivedN) aren't the culprits but rather the victims. In your case flyer_items table need the indexes: (id), (flyer_id), (id, flyer_id) and (flyer_id, id). I can't predict which one of the last two will be choosed by optimizer. 

Then derived tables produced by JOINs will get the suitable inherited indexes for further proceeding.