CODEWITHSUNDEEP
phpperformancemysqliprepared-statement
mlishn
mlishn

Reputation: 1667

Proper way of connection to MYSQLi using PHP (Performance)

I am familiar with PHP and MYSQLi connections and have read the PHP documentation including http://www.php.net/manual/en/mysqli.persistconns.php , but am wondering about the proper way (performance wise) to establish and close connections in MYSQLi using PHP.

What is the most efficient way of starting and closing mysqli connections?

What is the proper way to handle if the mysqli connection is lost during a query?

Here is my current setup:

  1. Opening a connection at the beginning of the php page
  2. Making all the necessary queries through other classes
  3. Closing the connection at the end of page

/*
public exampleQuery ($conn, $p1, $p2) {
  $stmt = $conn->prepare("SELECT id FROM tbl_name WHERE c1=? AND c2=?");
  $stmt->bind_param("ss", $p1, $p2);
  $stmt->execute();
  // etc..
}
*/


$mysqli_connection = $connectionClass->connectToMYSQLi();
//Execute queries using prepared statements
$queryClass->exampleQuery($mysqli_connection,$param1, $param2);
$queryClass->exampleQueryTwo($mysqli_connection,$param1, $param2);
//etc...
$connectionClass_>closeMYSQLiConnection($mysqli_connection);

Upvotes: 1

Views: 676

Answers (1)

Erwin Moller
Erwin Moller

Reputation: 2408

How does PHP handle the connections once the page is reloaded or accessed by a new user simultaneously?

PHP will also make sure the some variable you use in your script isn't used by a different user who happens to access the same page. The same goes for the database connection.

Accessing the database using some credentials (username/password) doesn't mean it can 'collide' with somebody else using the same credentials. The database is perfectly fine with running multiple connections simultaneously, even if they are all using the same credentials.

But this shouldn't be your problem/concern (as end-user of the functionality, being the developer), but it boils down to PHP forking processes or using a threadsafe module to serve each request. Then each process associated with each request starts a new session (or recycles one) to the database.

prepared statements:

Prepared statements are good. They offer the database an opportunity to use an executionplan that is stored on the database. When you use complex queries, this can give some speed advantages. They also protect you against SQL-injection.

So keep using the prepared statements.

How you structure them inside your code is up to you. The database doesn't care at all if you call the statements from some class or not. If you use the same prepared statements from a Java application, they would still be prepared statements (if the database supports them, that is.). The database would still have a executionplan ready for them.

So do it the way you like best (=most clear/concise to you).

There is a lot more to say about the subject, and I explained some concepts only partially correct, but I hope this brushes the big picture.

Upvotes: 1

Related Questions