How to optimize a select within an update?

Question

I created a stored-Procedure which updates the Drive table balances. At first I tried to use From Movimentacao within the update of the drive, only that returned error. I fixed this problem by making a FROM (SELECT * FROM Movimentacao) as L2. Only this generated another problem: slowness if the table is too big, because it keeps doing SELECT * FROM Movimentacao every hour. I tested with 10000 records and got very slow. I was breaking my head to build a query that was quick to update with select, but it’s a bit difficult.

Follow the stored-Procedure code in Mysql:

---

BEGIN

 set @a:=0;

 update Movimentacao d set d.ordem = (select @a:=@a+1) where id_conta = wconta
 order by data;

 UPDATE Movimentacao set saldo= (SELECT SUM(IF(operacao = 'Entrada', valor, -valor))
 FROM (SELECT * FROM Movimentacao) as L2
 WHERE L2.ordem <= Movimentacao.ordem and id_conta = wconta order by data,ordem)
 WHERE id_conta = wconta
 ORDER BY data,ordem;

END

Answer 1

I work a lot with query optimization for MS Sql Server. In general I solve a lot with the use of temporary tables limiting my data universe.

It seems that you are working with financial movement and that you need to update the balance with the sum of previous operations that record.

It seems that Mysql accepts CASE and INNER JOIN in Updates, so I suggest something like this:

BEGIN

 set @a:=0;

 update Movimentacao d set d.ordem = (select @a:=@a+1) where id_conta = wconta order by data;

CREATE TEMPORARY TABLE Temp_Movimentacao SELECT * FROM Movimentacao order by data;

  UPDATE Movimentacao AS M INNER JOIN 
      Temp_Movimentacao AS L2
      ON L2.ordem <= M.ordem AND id_conta = wconta
    SET M.saldo = 
        SUM(CASE L2.operacao = 'Entrada' THEN L2.valor ELSE L2.valor*-1 END)
    WHERE id_conta = wconta

END

I didn’t understand the use of ORDER in the UPDATE since it has no logical impact on my view since you are creating an order based on dates, so I took it. I would need to understand your problem a little better.

I hope I’ve helped.