In PHP, what happens in memory when we use mysql_query

Question

I used to fetch large amount of data using mysql_query then iterating through the result one by one to process the data. Ex:

$mysql_result = mysql_query("select * from user");
while($row = mysql_fetch_array($mysql_result)){
    echo $row['email'] . "\n";
}

Recently I looked at a few framework and realized that they fetched all data to an array in memory and returning the array.

$large_array = $db->fetchAll("select * from user");
foreach($large_array as $user){
    echo $user['email'] . "\n";
}

I would like to know the pros/cons of each method. It appears to me that loading everything in memory is a recipe for disaster if you have a very long list of items. But then again, a coworker told me that the mysql driver would have to put the result set in memory anyway. I'd like to get the opinion of someone who understand that the question is about performance. Please don't comment on the code, I just made it up as an example for the post.

Thanks

Answer 1

you're mixing matters.

• usability, which makes your code WAY smoother with arrays
• and unoptimized algorithm, when unexperienced programmer tend to load ALL data into script instead of making database to do all the calculations or get data in portions.

So. Frameworks do not fetch all data. They fetch just what programmer wrote.
So, a good programmer would not fetch large amounts of data into array. In these few cases when it really needed, one would use old line-by-line fetching (and every framework provide a method for this). In the all other cases smooth already-in-array fetching should be used.

Please also note that frameworks will never do such things like echoing data right inside of database loop.
Every good framework would use a template to output things, and in this case an array comes extremely handy.