The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.

Optimal indexes for this query:

ALTER TABLE `breakfast_costs` ADD INDEX `breakfast_costs_idx_date` (`date`);
ALTER TABLE `breakfast_orders` ADD INDEX `breakfast_orders_idx_date_user_id` (`date`,`user_id`);
ALTER TABLE `dinner_costs` ADD INDEX `dinner_costs_idx_date` (`date`);
ALTER TABLE `dinner_orders` ADD INDEX `dinner_orders_idx_date_user_id` (`date`,`user_id`);
ALTER TABLE `lunch_costs` ADD INDEX `lunch_costs_idx_date` (`date`);
ALTER TABLE `lunch_orders` ADD INDEX `lunch_orders_idx_date_user_id` (`date`,`user_id`);
ALTER TABLE `users` ADD INDEX `users_idx_id` (`id`);

The optimized query:

SELECT
        user_name,
        id,
        sum(cost) 
    FROM
        (SELECT
            users.name AS user_name,
            users.roll AS id,
            BC.individual_cost AS cost 
        FROM
            breakfast_orders BO,
            breakfast_costs BC,
            users 
        WHERE
            (
                BO.date = BC.date
            ) 
            AND (
                BO.user_id = users.id
            ) 
        UNION
        ALL SELECT
            users.name AS user_name,
            users.roll AS id,
            LC.individual_cost AS cost 
        FROM
            lunch_orders LO,
            lunch_costs LC,
            users 
        WHERE
            (
                LO.date = LC.date
            ) 
            AND (
                LO.user_id = users.id
            ) 
        UNION
        ALL SELECT
            users.name AS user_name,
            users.roll AS id,
            DC.individual_cost AS cost 
        FROM
            dinner_orders ddO,
            dinner_costs DC,
            users 
        WHERE
            (
                ddO.date = DC.date
            ) 
            AND (
                ddO.user_id = users.id
            )
    ) x 
GROUP BY
    user_name 
ORDER BY
    NULL