Migrations which iterate over large datasets can cause lengthy downtime

[johnnynotsolucky]

What are you trying to do?
Run database migrations without worrying about putting a store in maintenance mode for 3+ hours.

Additional Context
Migrations such as m200112_220749_cache_totalDiscount_totalTax_totalShipping iterate over every Order in the database. This migration runs roughly 8000 updates per minute on one of our databases, and with 1.4+ million records, it goes for more than 3 hours. At 10 million orders, the migration would take nearly a full day.

[lukeholder]

Hi @johnnynotsolucky

I can see there might be an issue with 1.4 million orders.

On Postgres I could write an single update query like the one below using common table expressions, but pre MySQL 8 doesn't support it...

WITH "totalsByOrderId" AS
(SELECT "orderId",
SUM(CASE
WHEN type = 'shipping'
		THEN amount 
		ELSE 0 
END)
AS "totalShippingCost",
SUM(CASE
WHEN type = 'discount'
		THEN amount 
		ELSE 0 
END)
AS "totalDiscount",
SUM(CASE
WHEN type = 'tax' AND included = false
		THEN amount 
		ELSE 0 
END)
as "totalTax",
SUM(CASE
WHEN type = 'tax' AND included = true
		THEN amount 
		ELSE 0 
END)
as "totalTaxIncluded"
FROM commerce_orderadjustments
GROUP BY "orderId"
)
UPDATE 
commerce_orders
SET
"totalDiscount" = 
(SELECT "totalDiscount" from "totalsByOrderId" WHERE "totalsByOrderId"."orderId" = commerce_orders.id),
"totalTaxIncluded" = 
(SELECT "totalTaxIncluded" from "totalsByOrderId" WHERE "totalsByOrderId"."orderId" = commerce_orders.id),
"totalTax" = 
(SELECT "totalTax" from "totalsByOrderId" WHERE "totalsByOrderId"."orderId" = commerce_orders.id),
"totalShippingCost" = 
(SELECT "totalShippingCost" from "totalsByOrderId" WHERE "totalsByOrderId"."orderId" = commerce_orders.id)

Will continue to look for a solution in MySQL