Persistent database connection, clearing the mysteries

What are persistent database connections?
There are two ways that you can use database on PHP,

1. Open a connection to the database, perform your query and then close it. Repeat the same thing for each and every query or at least each and every page.
2. Open a connection to the database, perform your query, leave the connection unclosed so that next time you can use same connection to perform other queries.

If you follow the second method, you are using a persistent database connection for your web application.

Exceptions:
If you are running PHP in CGI mode, you are surely not using a persistent connection to your database. Why? because in this mode, an instance of PHP itself is created to interpret a PHP page and destroyed as soon as its job is finished, making it impossible to have a persistent connection to the database.

Advantages of Persistent Database Connection:
Just imagine that you are sending same mail to 100 people. Wouldn’t it be better if you could just list all email addresses and send them at one click, instead of re-opening your mailer each time.
Using persistent database connection would provide a similar aid to PHP, it simply won’t have to reopen and then close, a connection to the database for each and every query or at least each and every dynamic pages in your web application.

Also, using persistent connection means using only single connection to the database at given time, you won’t be having like 100 connections to same database server simultaneously.

Disadvantage of Persistent connection:

1. It is just not available in some cases, for example if you run PHP on CGI mode.
2. You need to make sure that you have selected proper database and table before making each query, specially if you have multiple databases on same server.

Conclusion:
If you are in a dedicated hosting environment, using a persistent connection to the database could be the best thing for your web application. Both performance wise and efficiency wise.

How do I do it?
Simply don’t close the database connection.

Note: You have to use mysql_pconnect() to start a persistant connection to mysql database. This function makes sure that a previously opened connection is used, if available. Connections opened that way won’t be closed using mysql_close() function.

Got an objection, or question? Add a response below.