In PHP/MySQL should I open multiple database connections or share 1?

Question

I am wanting to hear what others think about this? Currently, I make a mysql database connection inside of a header type file that is then included in the top of every page of my site. I then can run as many queries as I want on that 1 open connection. IF the page is built from 6 files included and there is 15 different mysql queries, then they all would run on this 1 connection.

Now sometimes I see classes that make multiple connections, like 1 for each query.

Is there any benefit of using one method over the other? I think 1 connection is better then multiple but I could be wrong?

Answer 1

Creating connections can be expensive (I don't have a reference for this statement as yet Edit: Aha! Here it is) so it seems as if the consensus is to use fewer connections. Using a single connection for all queries on a single page seems to be a better choice than multiple connections.

Answer 2

In PHP+MySQL usually there is no much sence to use multiple connections per page (just slower and a little more RAM consumed).

The only way it might be useful is when you alter connection paremters which might interfer with other pages (like collation). But good PHP programs usually never do that kind of stuff.

Also, it is a good idea to enable persistent connections, so that 1 MySQL connection would be reused across multiples page executions.

Answer 3

If really depends on the level of activity you suspect the site will generate - if it's a high traffic web site, you'll soon run out of connections (unless you set the adjust MySQLs max connections to a stupidly high level, but that'll eventually grind the server to a halt).

I'd generally recommend that the front end of a web site should use a shared database object (singleton is your friend), as it doesn't require a great deal of discipline to write with this is mind and you won't waste time making connections. If you require additional concurrent queries on the backend, it shouldn't be that much of a deal as this isn't likely to be a highly trafficked area.

Answer 4

I don't see any benefit of using multiple connections, I 'd rather think it is a sign of bad structure. These are the reasons I can think of against using multiple connections:

• You have to initialize the database multiple times. Setting conection properties upon connection establishment (like SET NAMES UTF8) would have to be done on multiple line.
• It is definitely slower than a single connection.
• A non-technical reason: Someone working with your code will most probably not expect it and might spend hours debugging the connection properties he had set in another connection.

Having a global connection object (or a class providing one) is the much better approach in PHP.

Answer 5

Are you sure the classes that make multiple connections aren't just returning a reference to the already open connection when one is open? I've seen a lot of stuff structured that way. It really is better performance-wise to use only one connection per page.