Disconnecting from a MySQL database

When your PHP script is finished, PHP will automatically perform garbage collection on the objects and resources you used therein. As result of this, plus the fact that most scripts are over in less than a tenth of a second, it is generally not necessary to explicitly disconnect from your MySQL server or to hand-free the space allocated to your SQL results.

However, in the situation where you have a popular script that takes a long time to execute, perhaps over five to ten seconds, you should really try to do all you can to conserve memory and resources, and therefore it is smart to explicitly free up your MySQL resources rather than waiting to let PHP do it on your behalf.

To this end, there are two functions that you should be making use of: mysqli_free_result() and mysqli_close().

The mysqli_free_result() function is used to de-allocate memory that was used to store the query results returned by mysqli_query(). If you have particularly big queries being returned, then you should definitely be calling mysqli_free_result() if there is a big length of time between you being finished with the data and your script finishing execution. Here is how it works:

<?php
    $result = mysqli_query($db, "SELECT * FROM really_big_table;");
    ...[snip]...
    mysqli_free_result($result);
?>

The purpose of mysqli_close() is also to save computer resources, but another key reason for using it is because there is a limited number of connections that a MySQL server can accept, and if you have several clients holding connections open for no reason then the server may well need to turn away other, waiting clients. Naturally this is a bad thing, so, as with mysqli_free_result(), it is good to call mysqli_close() if you think there will be some time between your last database use and your script ending.

Using mysqli_close() is simple: you should already have captured the return value from mysqli_connect(), so just supply that to mysqli_close() and it will be closed.

So, here's a very simple example of mysqli_close() in action:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    ...[snip]...
    mysqli_close($db);
?>

As you can see it is very simple to use. As shown above, it is not really important - the script ends, and any open MySQL connections that aren't permanent connections will be closed automatically.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.