Mysql_Connect VS MySQL_Pconnect

mysql_connect VS mysql_pconnect

Persistent connections should be unnecessary for MySQL. In other databases (such as Oracle), making a connection is expensive and time-consuming, so if you can re-use a connection it's a big win. But those brands of database offer connection pooling, which solves the problem in a better way.

Making a connection to a MySQL database is quick compared to those other brands, so using persistent connections gives proportionally less benefit for MySQL than it would for another brand of database.

Persistent connections have a downside too. The database server allocates resources to each connection, whether the connections are needed or not. So you see a lot of wasted resources for no purpose if the connections are idle. I don't know if you'll reach 10,000 idle connections, but even a couple of hundred is costly.

Connections have state, and it would be inappropriate for a PHP request to "inherit" information from a session previously used by another PHP request. For example, temporary tables and user variables are normally cleaned up as a connection closes, but not if you use persistent connections. Likewise session-based settings like character set and collation. Also, LAST_INSERT_ID() would report the id last generated during the session -- even if that was during a prior PHP request.

For MySQL at least, the downside of persistent connections probably outweighs their benefits. And there are other, better techniques to achieve high scalability.

---

Update March 2014:

MySQL connection speed was always low compared to other brands of RDBMS, but it's getting even better.

See http://mysqlserverteam.com/improving-connectdisconnect-performance/

In MySQL 5.6 we started working on optimizing the code handling connects and disconnects. And this work has accelerated in MySQL 5.7. In this blog post I will first show the results we have achieved and then describe what we have done to get them.

Read the blog for more details and speed comparisons.

Which is better: mysql_connect or mysql_pconnect

If you are going to write a web page there is no need of persistent connection. It takes too much resources. Use mysql_connect. Minimize the time your db connection is open and not used as much as you can. Open, fetch what you want, close. It doesn't need to stay open while the users are just reading. The connection will be used eventually if they respond - INSERT/go to another page..

Here are some good points about NOT USING persistent connection in web applications

• When you lock a table, normally it is unlocked when the connection closes, but since persistent connections do not close, any tables you
  accidentally leave locked will remain locked, and the only way to
  unlock them is to wait for the connection to timeout or kill the
  process. The same locking problem occurs with transactions. (See
  comments below on 23-Apr-2002 & 12-Jul-2003)

• Normally temporary tables are dropped when the connection closes, but since persistent connections do not close, temporary tables aren't
  so temporary. If you do not explicitly drop temporary tables when you
  are done, that table will already exist for a new client reusing the
  same connection. The same problem occurs with setting session
  variables. (See comments below on 19-Nov-2004 & 07-Aug-2006)

• If PHP and MySQL are on the same server or local network, the connection time may be negligible, in which case there is no advantage
  to persistent connections.

• Apache does not work well with persistent connections. When it receives a request from a new client, instead of using one of the
  available children which already has a persistent connection open, it
  tends to spawn a new child, which must then open a new database
  connection. This causes excess processes which are just sleeping,
  wasting resources, and causing errors when you reach your maximum
  connections, plus it defeats any benefit of persistent connections.
  (See comments below on 03-Feb-2004, and the footnote at
  http://devzone.zend.com/node/view/id/686#fn1)

MySQL persistent connections and advantages of mysql_pconnect?

Using a persistent connection leaves the connection open after the script has finished executing. Opening and closing connections over and over causes overhead, while small, that will eventually mount up as the number of requests go up.

However, if you read the manual page for mysql_pconnect it states:

• If PHP and MySQL are on the same server or local network, the connection time may be negligible, in which case there is no advantage to persistent connections.

If this is the case it may not be worth the trouble changing your code.

You can find more detailed information on persistent connections at the same site as above.

mysql_pconnect vs odbc_connect (PHP)

mysql_pconnect mysql_pconnect() is like mysql_connect() except, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()). You can read about it here.

odbc_connect just used to make db connections from php script. As its signature shows

resource odbc_connect (string $dsn, string $user, string $password [, int $cursor_type ])

it needs the database source name (dsn) for the connection. Alternatively, a DSN-less connection string can be used.
You can find more examples here

For your second question, yes odbc_connect is portable to other databases.

mysql_connect vs mysqli_connect

How about structuring your class like this:

class Database {

  public function __construct ( $server, $dbuser, $dbpasswd, $dbname ) {
    $this->dbhandle = new mysqli($server, $dbuser, $dbpasswd, $dbname);
  }

  public function Select ($query) {
    $result = $this->dbhandle->query($query);

    while ( $row = $result->fetch_row() ){
      // Whatever you're doing here...
      //$consulta = htmlentities($row[0]);
    }

    return $consulta; 
  }

}

So you could use it with this code:

class Query{
  public function __construct(){
    require('conex/classDatabase.php');

    $this->Database = new Database($host, $user, $pass, $dbname);
  }

  public function Query ($email) {
    $query = "SELECT user_email from table where email='".mysql_real_escape_string($email)."'";
    $consulta = $this->Database->Select($query);
    return $consulta;
  }
}

I've included the object oriented syntax in my examples. Since you're using objects anyway, you'll probably get along with it.

---

Related Topics