Arithmetic operations in PHP or Mysql?

Question

What would be the good practice when doing arithmetic operations that come from a database query, for example:

SELECT 
    valor_bruto_atual,
    valor_investido,
    (valor_bruto_atual / valor_investido) AS rendimento,
    (valor_bruto_atual - valor_investido) AS rendimento_liquido
FROM
    ativos_extratos; 

In this example I only return 2 columns and 2 calculations (division and subtraction)

In this next example using the Laravel model I made a foreach going through the same query (without the calculations).

echo "<pre>";
foreach($ativos->get() as $ativo){
    echo $ativo->valor_bruto_atual;
    echo $ativo->valor_investido;
    echo $ativo->valor_bruto_atual / $ativo->valor_investido;
    echo $ativo->valor_bruto_atual - $ativo->valor_investido;            
}
echo "</pre>";
dd('stop');

PHP is only illustrative, even in my test environment this example being fully functional.

Now comes the real question, what would be the best way to do these calculations, in Mysql itself or leave it to PHP to do it?

What would be the good practice for such?

Answer 1

As a rule, it’s best to keep it in the database. If you do on the application server (PHP) you have the overhead of transferring an amount of data that can be large for only after doing the calculation, which the database itself is able to manage when processing your query.

It is hard to call this good practice, a term that has been so often frowned upon here at Sopt, but I believe that this question of overhead in general has to be considered.

The @Lucasprochnow raised a point that is to scale the bank (I believe that at this moment fit optimizations either in queries or in the application) and ended up bumping into an important point, which is the maintenance cost of this code. I just thought about the volume of data. He’s right, it’s a real choice.

Answer 2

In my opinion, it is better to do these calculations at the machine level, ie in PHP.

With few data these calculations will not make a difference in performance or cost ($$) to maintain DB, but as the data increases, these calculations will be very expensive at the DB level. In short, it is much cheaper and easier you scale an application machine than bank.

But like I said, it’s my opinion. I think this case doesn’t exist right/wrong, there is a choice (;

@Piovezan raised another important point about overhead, only reinforces that it is a matter of choice