5

What is the importance of using:

$stmt->free_result();
$stmt->close();

After a database call using prepared statments like this: 

$mysqli=new mysqli("database", "db", "pass", "user");

$stmt = $mysqli->prepare("SELECT email FROM users WHERE id=? ");
$stmt->bind_param('i',$_SESSION['id']);
$stmt->execute();
$stmt->bind_result($email);
while($stmt->fetch()){
     echo $email;
}
$stmt->free_result(); //why do i need this?
$stmt->close();       //why do i need this?

Im asking because I do not see any noticeable performance degradation without them. Are those commands usually only used for when I store the result using:

$stmt->store_result();

Like this:

$mysqli=new mysqli("database", "db", "pass", "user");

$stmt = $mysqli->prepare("SELECT email FROM users WHERE id=? ");
$stmt->bind_param('i',$_SESSION['id']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($email);
while($stmt->fetch()){
     echo $email;
}
$stmt->free_result(); //why do i need this?
$stmt->close();       //why do i need this?

Ultimately the question comes down to when is the appropriate time to use freeresult() and close()?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Dan Kanze
  • 18,485
  • 28
  • 81
  • 134

1 Answers1

1

The free_results statement tells the database engine it can release the result set.

When executing your statement, an iterator is created. The client (your app) iterates each result by either downloading them one by one or in chunk.

This allows you app to iterate millions of record without downloading all the results in one chunk.

EDIT

Free result will free memory on the client side. Useful if a single record is very large and memory needs to be freed.

See: http://php.net/manual/en/function.mysql-free-result.php

Martin Samson
  • 3,970
  • 21
  • 25
  • 1
    Nope, it doesn't tell anything to the database – zerkms Jan 25 '12 at 01:13
  • So I can use freeresult() at the end of any statement which I do not need to storeresult() for? – Dan Kanze Jan 25 '12 at 01:14
  • 1
    @Dan Kanze: as long as you don't experience any issues - don't do that, don't waste CPU cycles for nothing – zerkms Jan 25 '12 at 01:15
  • @zerkms So I guess the question then.. is when is the appropriate time to use freeresult() and close()? – Dan Kanze Jan 25 '12 at 01:18
  • @Dan Kanze: when you write a long-running script that consumes a lot of memory – zerkms Jan 25 '12 at 01:20
  • "Useful if a single record is very large and memory needs to be freed." --- usually it is 16 and more mb allocated for the script. Cannot imagine the case when a single row wouldn't fit such limits ;-) – zerkms Jan 25 '12 at 01:23
  • @zerkms I can see that for freeresult() then I guess, but what about close()? Are they always used hand-in-hand? And if thats true, is there any benifit for using close() on a statment that doesnt use alot of memory? – Dan Kanze Jan 25 '12 at 01:26
  • @Dan Kanze: there is no benefit - you just do the unnecessary work, thus make your script actually *slower* – zerkms Jan 25 '12 at 01:29
  • @zerkms So the final answer is use freeresult() and close() for scripts that consume alot of memory, but requests that consume a small to moderate amount there is no need for either? And this also applies to scripts that use storeresult()? – Dan Kanze Jan 25 '12 at 01:31
  • @Dan Kanze: most likely - yes. But I've never used `mysqli_store_result` and still cannot get the reason why to use it – zerkms Jan 25 '12 at 01:33