3

In my PHP program I need to hit the database between 0 and 3 times on any given webpage request. I am using PDO to interact with MySQL. First I create a database connection using something like this:

$dbh = new PDO("mysql:host=$hostname;dbname=animals", $username, $password);

Then I do what I need to do and close the connection like this:

$dbh = null;

I open and close a connection 0-3 times right now, the same number of times I need to interact with MySQL.

My question is, should I be re-using this connection instead? My queries are not one after another, they are scattered throughout my program and I don't really think it would be easy to run them one right after another.

So is it better to create a new database connection and close it for each interaction (query) or to just leave the connection open and reuse it? Does it make any difference?

Thanks!

3 Answers3

3

For a typical website page, you should be reusing the same connection for all queries.

It's not worth it to spend time disconnecting and reconnecting.

Matthew
  • 47,584
  • 11
  • 86
  • 98
1

Unless your pages take a huge amount of time to run (huge being relative), then there's no point in relinquishing a connection. You'll end up wasting more cycles on connectiong/disconnecting than you do actually executing queries. MySQL's pretty lightweight as far as connections go, but it still adds up.

Marc B
  • 356,200
  • 43
  • 426
  • 500
-1

The site says: "You must have 50 reputations to comment". My reputation is pretty bad so I can't leave a comment, but I want to add a notice on:

For a typical website page, you should be reusing the same connection for all queries.

Absolutely right, but in case of need atypical script (e.g. compare tables between a remote database and local database) one can't use the same connection. Must close first connection and establish new one or have 2 separate connections (using different names) at the same time. I prefer the second option in this case because of closing connection using "$dbh = null;" it can be a bit difficult in some cases. (PDO closing connection)

Here is explanation [from documentation: https://www.php.net/manual/en/pdo.connections.php ] why should be careful with "$dbh = null;" (it should be closed all instances too):

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here
$sth = $dbh->query('SELECT * FROM foo');

// and now we're done; close it
$sth = null;
$dbh = null;
?>

So, only $dbh = null; (without nullifying instances) is not a good idea.

Levac Onlajn
  • 251
  • 2
  • 7