Does using a PHP class to run mysql queries like this bad?

I been looking at some database (MySQL) wrapper classes, A lot of them work like this,

1) Run the sql query

2) while fetching associative mysql array, they cyle through the results and add them to there own array

3) then you would run the class like this below and cycle through it's array

<?php  
$database = new Database();

$result = $database->query('SELECT * FROM user;');

foreach ($result as $user){
    echo $user->username;
}

?>

So my question here, is this not good on a high traffic type site? I ask because as far as I开发者_开发知识库 can tell, mysql is returning an array which eats memory, then you are building a new array from that array and then cycleing through the new array. Is this not good or pretty much normal?

---

The short answer is: it's bad, very bad.

The trouble is you pay a nasty performance hit (cycles and memory!) by iterating over the results twice. (what if you have 1000 rows returned? you'd get all of the data, loop 1000 times, keep it all in memory, and then loop over it again).

If you refactor your class a bit you can still wrap the query and the fetch, but you'll want to do the fetch_array outside the query. In this case, you can discard each row from memory as soon as your done, so you don't need to store the entire result set, and you loop just one time.

IIRC, PHP won't load the entire MySQL result set into memory, basically, when you call mysql_fetch_array you're asking for the next row in the set, which is loaded only upon asking for it, so you're not paying the memory hit for the full set (on the PHP side) just by running the original query. The whole result gets loaded into memory when you use mysql_query (thanks VolkerK), but you're still paying that CPU cost twice and that could be a substantial penalty.

---

The code is fine.

foreach() just moves the array pointer on each pass.

You can read all about it here:

http://php.net/foreach

For a deeper understanding, look at how pointers work in C:

http://home.netcom.com/~tjensen/ptr/ch2x.htm

Nothing is copied, iteration is almost always performed by incrementing pointers.

---

This kind of query is pretty much normal. It's better to fetch only a row at a time if you can, but for normal small datasets of the kind you'd get for small and paged queries, the extra memory utilisation isn't going to matter a jot.

SELECT * FROM user, however, could certainly generate an unhelpfully large dataset, if you have a lot of users and a lot of information in each user row. Try to keep the columns and number of rows selected down to the minimum, the information you're actually going to put on the page.