autoupgrade query (1.7.7.0) for total_refunded_tax_excl is totally bottleneck on huge PrestaShop

[ShaiMagal]

Prerequisites

• I understand and accept the project's code of conduct.
• I have already searched in existing issues and found no previous report of this bug.

Describe the bug and add attachments

This query is crazy, it isn't optimized, this should be rewriten another way.
When we update via autoupgrade, it can get stuck for XX minutes, because query is not optimized and autoupgrade failed ("MySQL server has gone away"), we must remove this from upgrade script (./upgrade/sql/1.7.7.0.sql) and run manually independently after upgrade.

Environment:

• high end server
• order_detail number of rows - 500 000
• orders number of rows - 100 000
• order_slip_detail number of rows - 10 000

UPDATE
    `order_detail` `od`
SET
    `od`.`total_refunded_tax_excl` = IFNULL((
        SELECT SUM(`osd`.`amount_tax_excl`)
        FROM `order_slip_detail` `osd`
        WHERE `osd`.`id_order_detail` = `od`.`id_order_detail`
    ), 0),
    `od`.`total_refunded_tax_incl` = IFNULL((
        SELECT SUM(`osd`.`amount_tax_incl`)
        FROM `order_slip_detail` `osd`
        WHERE `osd`.`id_order_detail` = `od`.`id_order_detail`
    ), 0)

Expected behavior

Optimize this query for big eshops

Steps to reproduce

1. create huge eshop and try update for example from 1.6.1.4 to 1.7.7.0

PrestaShop version(s) where the bug happened

1.7.7.0

How you installed PrestaShop

No response

PHP version(s) where the bug happened

7.2

If your bug is related to a module, specify its name and its version

autoupgrade

Your company or customer's name goes here (if applicable).

https://www.openservis.cz/