MySQL Optimizations using CodeIgniter Active Records with Multiple Databases

I开发者_开发技巧 am using multiple databases using CodeIgniter Active Records class. I had to disable persistent connection with MySQL, because CodeIgniter Active Records class can't make persistent connections using multiple databases.

I am looking at the load of my database, it seems like it's using most of it's queries calling "change database" and I am not sure if that's a good sign.

How can I optimize this without having to call "change database" all the time?

It's not as user friendly as most of the Active Record commands, but you can call the SQL directly like this:

$query = $this->db->query("SELECT * FROM database_a.some_table");
$query2 = $this->db->query("SELECT * FROM database_b.another_table");

Are you using queries that reference both databases? If not, it's not too difficult to load a new DB instance for the second database. I.e. you'd still use $this->db for the first, but you could have $this->db2 for the second. I honestly have no idea if that would trigger the "change database" command you're talking about, but it would be MUCH more sustainable code, at least. CI could keep its connections to each database open for the duration of the script (not a persistent connection), and it seems your problem would be fixed.

I've never needed multiple mysql databases in a single app, so this is entirely a guess based on what I've seen with, say, one mysql db and another being an sqlite db.