Using MySQLi - which is better for closing queries

Question

I have a habit of keeping my variable usage to a bare minimum. So I'm wondering if there is any advantage to be gained by the following:

$query = $mysqli->query('SELECT * FROM `people` ORDER BY `name` ASC LIMIT 0,30');

// Example 1
$query = $query->fetch_assoc();

// Example 2
$query_r = $query->fetch_assoc();
$query->free();

Now if I'm right Example 1 should be more efficient as $query is unset when I reassign it which should free any memory associated with it. However there is a method (MySQLi_Result::free()) which frees associated memory - is this the same thing?

If I don't call ::free() to free any memory associated with the result but unset it by reassigning the variable am I doing the same thing? I don't know how to log this sort of thing - does anyone have some ideas?

Answer 1

The manual seems to suggest that you should still be using free() to release the memory. I believe the reasoning is that free() is freeing the memory in MySQL, not in PHP. Since PHP can't garbage-collect for MySQL, you need to call free().

Answer 2

That makes alot more sense - thanks.

Answer 3

Hi Ross,

Example 1 dissociates the $query variable from the MySQL result. The MySQL result still exists in memory, and will continue to exist and waste memory until garbage collection occurs.

Example 2 frees the MySQL result immediately, releasing the used resources.

However, since PHP pages are generally short-lived with small result-sets, the memory saved is trivial. You will not notice a slowdown unless you leave a ton of results in memory over a long period of time on pages that run for a long time.

Brian, PHP can garbage collect the MySQL result, it just doesn't happen immediately. The result lives in PHP's memory pool, not in the MySQL server's.

(the locality of memory when using unbuffered queries is slightly different, but they're so rarely used in PHP as to not be worth mentioning)