CODEWITHSUNDEEP
phpmysqldatabaseperformancemysqli
andreas
andreas

Reputation: 8004

Most performant solution for multiple databases in MySQL and PHP

I have four databases with different tables, let's call the databases admindata, userdata, pagedata and mediadata.

At the moment I use one instance of mysqli for all databases:

$mysqli = new mysqli(...);

I keep this instance open until the end of the page load. I was not sure about the performance so I made databases switches like this when I had multiple queries in one script:

$mysqli->query('SELECT fields FROM database1.table1');
$mysqli->query('SELECT fields FROM database2.table2');

instead of:

$mysqli->select_db('database1');
$mysqli->query('SELECT fields FROM table1');
$mysqli->select_db('database2');
$mysqli->query('SELECT fields FROM table2');

What of those two is better in performance? Or is it even better to hold one instance of mysqli for every database like this:

$mysqli->query('SELECT fields FROM table1');
$mysqli2 = new mysqli(...);
$mysqli2->query('SELECT fields FROM table2');

EDIT:

On php.net someone wrote two years ago:

In some situations its useful to use this function for changing databases in general. We've tested it in production environment and it seams to be faster with switching databases than creating new connections.

http://ch2.php.net/mysqli_select_db

It would be nice to hear more about it.

Upvotes: 0

Views: 148

Answers (1)

Irek Kubicki
Irek Kubicki

Reputation: 57

I was teached that is better to hold separate connection per database / per user. Not just because of matter of security, but also a flexibility of your code. In the future, if you would like to start using ORM, it will be very difficuilt to rewrite the code that uses single connection for several databases. It may also help with debugging the database problems.

On other hand there's potential problem of having many connections per request. In the project im leading, we do use separate connections, but we pay attention on closing them when they are not needed anymore.

I think there's no universal solution, and all depends from your own needs and requirements.

Upvotes: 1

Related Questions