How does database connection works in php-mysql

Question

Is it better to close the connection after each query is executed or put the connection as is it, then php will automatically close that connection.

Which one is better and why?

Answer 1

Open the connection just once. Opening and closing a connection takes time too. And as you already said, PHP closes open connections at the end of the runtime automatically.

So just call mysql_connect whenever you need a connection and let PHP close it at the end. mysql_connect checks for already existing connections so you don’t need to worry that calling mysql_connect with the same parameters will open a new connection every time. You can also use persistent connections that can be used for more than just one script execution.

Answer 2

It may be better to use a persistent connection or a pool of connections.

Answer 3

It's usually good practice to close the connections that you open, tidying up as you go along.

Whether you want to open and close one for each query will depends on your application really. If it only interacts very infrequently with the database, then it might be best to do it this way. Or you might want to hold a pool of connections open for use by all, only opening a new one when all the other ones are currently being used.

Answer 4

From what I have the experience is better to leave the connection opened. But this is dependent on behaviour of your application. If you are doing huge number of calculations or connection external services which can take some time to finish, then is better to close the connection and open it again after you finish the time consuming part. If you don't have big number of visitors where you can hit the limit of number of sql connections then leave the connection opened all the time. It takes some time to open it again.

Answer 5

Connect to your database once, during script initialization; keep the connection open during your script's execution and send further queries through it.

The above is a typical usage scenario, where you have a short-running PHP script (probably on a webserver) executing multiple SQL queries. Unless your script runs for longer than a few hours, don't worry about closing the connection between queries.

Every time you connect to the SQL server, both the PHP script and the server need to go through a (relatively) complex negotiation: establish a connection over the network, check that both sides want to speak MySQL, check that the script has permissions to connect, et cetera. Keep the db connection up, it's much faster and more efficient.

Answer 6

It doesn't really matter. If your PHP script is going to close the connection for you at the end of the script there isn't really much point doing it yourself.

The only reason you would want to put that extra code in the script to close the connection after you have done all your queries is if you want to free up a little memory, e.g. your script is already memory hungry using libraries like GD2.

Closing the connection after each query, and opening another to do another query IS memory hungry, and a huge waste of time. All in all, don't bother really!

Answer 7

Let the connection open, if your script use the connection randomly. If there is group of jobs which are using the connection with certain time gap then you can close the connection after each group jobs.