CODEWITHSUNDEEP
php
Marco Mura
Marco Mura

Reputation: 582

Better close Pdo connection on no persistent connection? & Pdo Connection Performance

I am more used to mysql_query command than Pdo ones, however I'm going to use them in a new project.

I'd like to know if for not persistent connection it's better to close the connection or if it doesn't create problem to leave it (like on mysql_query).

The second question is: There is a difference on performance between the complete Pdo Connector

 $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

and the dbless one?

 $dbh = new PDO('mysql:host=localhost', $user, $pass);

I need for a web application to use more than one db, i'd choose to put on each query the dbname.dbtable and not open two or more connection each one on a different db.

There are other ways eventually?

Upvotes: 0

Views: 926

Answers (3)

Marc B
Marc B

Reputation: 360682

The db-less version is actually less efficient, if you later on have to do a selectdb()-type call. That'd have to be sent over as a separate query, which has much higher overhead than sending it over in the original call.

Note that you don't HAVE to set a default DB. You can trivially write all of your queries as absolute

SELECT fieldname FROM dbname.tablename ...

Also note that unless you create persistent connections, PHP will automatically destroy the DB connection when the script exits and the cleanup operations kick in. Unless you're using persistent connections for the DB, it is literally impossible to keep your connection open when the script exits.

Persistent connections may sound good in theory, but they open up a whole whack of problems. e.g. You start a transaction, do a bunch of stuff, and then the script dies for some reason. Since it's done with a persistent transaction, the connection to the DB stays open, the transaction stays in flight, and then some OTHER php script fires up and is assigned that stale connection from the pool.

Now you've got a problem. The original script's transaction is still active, and in some indeterminate intermediate state. The new script has NO idea that it's getting this trash-filled connection, and will simply start its own operations. You can quite easily end up with deadlocks, committing bad data, etc... because of this.

In general, persistent connections in MySQL should be avoided. Its connection protocol is actually quite light and has very little overhead. The minute savings from using persistent conn pools is vastly outweighed by the dangers of getting stale/garbage-ridden connections from previous failed scripts.

Upvotes: 2

deceze
deceze

Reputation: 522091

  1. The connection will automatically be closed at the end of the script (or when $dbh gets unset or falls out of scope). That's the same as it was for ext/mysql and for virtually all other database connectors. You could close the connection manually when you know you're done with it to free up the minimal resources it may take up, but in practice it's often hardly worth it.
  2. The only thing dbname does is tell the database server which database you want to use. The only difference this makes is that you do not need to use the db.table.column format every time you refer to a table, you can simply use table.column and it will understand which database you're talking about. There's no performance difference worth mentioning.

Upvotes: 4

Jay Blanchard
Jay Blanchard

Reputation: 34416

From the docs -

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

So it will close when your script ends, but I have found that it is always good practice to close PDO connections after each query.

Upvotes: 0

Related Questions