CODEWITHSUNDEEP
phpmysqlperformance
Angolao
Angolao

Reputation: 992

The best efficient method to connect MySQL from PHP?

It is very common that using PHP to connect MySQL. The most common method is like this:

$sqlcon=mysql_connect("localhost","user","pw");
mysql_select_db('database');
$sqlcomm=mysql_query("SELECT id FROM bd");
while($row=mysql_fetch_row($sqlcomm))
{
   //do something
}
mysql_close($sqlcon);

I think this is the fastest direct way to connect MySQL. But in project, there will have too many MySQL connections in php script, we should use "mysql_connect("localhost","user","pw")" code to connect MySQL in every php script. So you will like to build a MySQL class or function in a file to connect MySQL:

function connect( $query )
{
   $sqlcon=mysql_connect("localhost","user","pw");
   mysql_select_db('database');
   $sqlcomm=mysql_query($query);
   while($row=mysql_fetch_row($sqlcomm))
   {
       //do something.
   }
   mysql_close($sqlcon);
}

and then include into your project using include() for connection.

include('connect.php');
$data = connect('SELECT id from db');

OK, in this way, the code is look better. But using include() function will let PHP to read and execute other php script files, a I/O operation on harddisk again, it will also slow down the performance.

If the webpage is 100PV/s, php will read and execute a one php script 100 times/s in first method, but read and execute php script 200 times/s in this method!

I here show a simple example for only one query. Try image a high network multi-query environment.

Dose any one have other better way to make MySQL connection more easier and more efficient?

Upvotes: 5

Views: 9497

Answers (6)

cypher
cypher

Reputation: 6992

Have a look at Dibi.

Upvotes: 1

symcbean
symcbean

Reputation: 48387

But in project, there are too many MySQL connections, we should type Username and Password code each time

There are lots of things wrong with this statement - even if you don't count the grammar.

If you mean that you have multiple servers with different datasets on them, then you should definitely consider consolidating them or using the federated engine to provide a single point of access.

Opening a new connection and closing it each time you run a query is very inneficient if you need to execute more than one query per script.

Realy you need to spend a lot of time thinking about why you need multiple database connections and eliminate them, but in the meantime, bearing in mind that connections are closed automatically when a script finishes.....

class connection_pool {
  var $conxns=array(
          'db1.example.com'=> 
              array ('host'=>'db1.example.com', 'user'=>'fred', 'password'=>'secret'),
          'db2.example.com'=> 
              array ('host'=>'db1.example.com', 'user'=>'admin', 'password'=>'xxx4'),
           ....
         );
  function get_handle($db)
  {
     if (!array_key_exists($db, $this->conxns)) {
          return false;
     }
     if (!@is_resource($this->conxns[$db]['handle'])) {
          $this->conxns[$db]['handle']=mysql_connect(
                 $this->conxns[$db]['host'],
                 $this->conxns[$db]['user'],
                 $this->conxns[$db]['password']
                 );
     }
     return $this->conxns[$db]['handle'];
  }
}

(NB never use 'USE database' if you have multiple databases on a single mysql instance - always explicitly state the database name in queries)

Upvotes: -1

Frantisek
Frantisek

Reputation: 7703

You don't really need to open that many connections. You just open 1 connection at the start of your script (before <body> gets generated, let's say), and then close it at the end of your script (after </body> is generated, let's say). That leaves you with only 1 connection. In between, you can execute as many queries as you need.

Upvotes: 3

Ian Wood
Ian Wood

Reputation: 6573

Have you looked at using PDO? it does connection pooling and what not andnot limited to mysql...

Upvotes: 1

Headshota
Headshota

Reputation: 21449

performance lack would be insignificant. you must be concerned more about correct approach to the structure of your code than performance.

you can move your host/user/password into constants into separate files and include wherever you need them, more over you can use some design patterns for database object. like Singleton or Factory. they will provide more flexibility to your system.

Upvotes: 0

Michael J.V.
Michael J.V.

Reputation: 5609

You use a class that opens a MySQL connection (username / password / db is inherited from some sort of configuration file) and when you query the db - it establishes a connection. That leads you on to using a framework that uses certain programing paradigms and so forth.

Also, you shouldn't worry about performance decrease because you're including a file. That should be the least of your worries. OS is doing many IOs, not just with the hard disk, your 1 file include won't be noticeable.

If you're asking whether there's more efficient way of connecting to a MySQL db without using mysql_, mysqli_, odbc or PDO - no, there isn't.

Upvotes: 0

Related Questions