How consuming is PHP Comparing to MySQL when summing rows

Question

Basically I would have done it directly from MySQL, but I am working with someone else's code and I am bound to his mistakes.

Just out of curiosity, let's say I have 20 million rows in my database, and I sum them all in a PHP loop (doesn't matter names of columns).

How consuming is that?

For now I bypassed the issue using:

ini_set('memory_limit','2000M');

Let's try be more specific with an example:

Assume I have a table called deposits that has the columns user_id, deposit_amount.

There are two ways to sum the amount of all the users.

1) Using MySQL:

SELECT SUM(`deposit_amount`) FROM `deposits`)

2) Using PHP:

$deposits = getAllDeposits();
foreach ($deposits as $deposit)
{
    $sum += $deposit;
}

Besides it risks script abortion when memory or time limits are hit. After all it means all those values have to be kept in memory! — arkascha, Apr 30 '14 at 12:12
To test this yourself [benchmark how long it takes](http://stackoverflow.com/questions/8291366/how-to-benchmark-efficiency-of-php-script) each way and you will have your answer. — Digital Chris, Apr 30 '14 at 12:13
We're looking at probably tens of minutes vs. a few seconds. — Joachim Isaksson, Apr 30 '14 at 12:14
If you were to do it in PHP then you should at least use a cursor. Reading 20 million records into memory is crazy... and will probably not fit anyway. — Sverri M. Olsen, Apr 30 '14 at 12:17
@JacobCohen Probably because this is something you could easily figure out yourself. — Sverri M. Olsen, Apr 30 '14 at 12:18
Doing this in PHP is fine when you have a hundred rows, say. @DigitalChris has the right idea - try it yourself, and if it takes too long, then it's not the right way of doing it `:)`. — halfer, Apr 30 '14 at 12:20
And yes, if you have to sum in PHP, at least throw the rows away, rather than trying to hold it all in RAM! — halfer, Apr 30 '14 at 12:21

score 0 · Accepted Answer · answered Apr 30 '14 at 12:13

0

Since you are going to read them all into memory and then doing a loop with a bunch of memory operations, very consuming.

answered Apr 30 '14 at 12:13

Limiter

1 Answers1