I got a view “project_sum” in my mariaDB database. This view got 4 problematic columns, of which each calulates the sum of accounting_records. Each of them sums up values of up to 200 accounting_records.

In the end the view gets about 5.000 rows in total. So it have to calculate 5.000×4 sums for around 5.000×200 accounting_record values.

This is the query of the view:

DROP VIEW IF EXISTS project_sum;

CREATE VIEW project_sum AS
SELECT
    project_id,
    SUM(CASE
                WHEN planned_actual_indicator = 'planned' AND datev_type = 'incoming'
                THEN (value_in_euro)
                ELSE 0
                END
        ) AS 'sum_incoming_planned',
    SUM(CASE
                WHEN planned_actual_indicator = 'actual' AND datev_type = 'incoming'
                THEN (value_in_euro)
                ELSE 0
                END
        ) AS 'sum_incoming_actual',
    SUM(CASE
                WHEN planned_actual_indicator = 'planned' AND datev_type = 'outgoing'
                THEN (value_in_euro)
                ELSE 0
                END
        ) AS 'sum_outgoing_planned',
    SUM(CASE
                WHEN planned_actual_indicator = 'actual' AND datev_type = 'outgoing'
                THEN (value_in_euro)
                ELSE 0
                END
        ) AS 'sum_outgoing_actual'
FROM accounting_record USE INDEX (is_initial)
WHERE accounting_record.is_initial = false
GROUP BY project_id;

Unfortunately there is a need for the sums to be always up to date, when opening the view. There is a zero tolerance policy for wrong values, since it is about money.
Therefore we have to calculate the sums on-the-fly and can’t store them pre-calculated in a database table. This makes loading the page very slow and the loading performance very bad.

One idea I had was to update pre-calculated sums everytime a new accounting_record will be pushed, but then we thought about racing conditions, which may eventually causing one summation missing the value of another record, which was added at the same time.

We have already indexed the keys of the records we sum up: is_initial, planned_actual_indicator and even datev_type and project_id. But that also didn’t help much. Is there any way to improve this query so that the performance is better?