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!

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

-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.

Improve this answer

Comments