Multiple databases

[eluser]smilie[/eluser]
Hi all,

Has anyone experience with multiple databases within CI?

I have 2 database groups, let's say:
db1 (this one is set as active group in database.php config);
db2;

Then, in my code I use:

Code:

$db2 = $this->load->database('db2',TRUE);
$db2->query("SELECT * FROM table2");
$res = $db2->result_array();

However, in the above - CI uses db1 parameters - and ofcourse can not execute query (missing tables).

Why - oh why, does CI do this?!
Is there a way to dump which database params are beeing used?

Thanks!
Smilie

[eluser]smilie[/eluser]
I just printed $db2 and in it I see that it has good connection parameters (which belong to db2).
Why the %^#@ is CI then targeting db1?

Regards,
Smilie

[eluser]WanWizard[/eluser]
Disable persistent connections.

If you have that enabled, PHP will reuse the connection to the database engine for the second database, which means you have to prefix all table names with the database name, so that the server knows which database you are referring to.

[eluser]smilie[/eluser]
WanWizard,

Ah, brilliant! That helped :-)

is there any performance gain from using persistent connection? Or, in other words - does it pay off to prefix all table names in the code?

Thank you!
Smilie

[eluser]WanWizard[/eluser]
Depends on the platform, whether or not the webserver and the database run on the same platform, the number of page requests (concurrent connections), etc. So, very difficult to say.

I'd say it's going to be noticable when you have a web farm talking to a database farm, and you have a lot of concurrent connections. Just ran a test with ApacheBench on my dev server (1000 requests, 20 concurrent), and I can hardly see a difference in the average statistics.

What you could try (don't know if it will work), is use a persistent connection for your primary database, and no persistent connection for the second one.

[eluser]smilie[/eluser]
Hi WanWizard,

Setup of primary db connection to persistant is holding up :-)

Apache & MySQL are (and will remain in future) on same server, so basically there is not urgent need to push persistent for both databases.

Thank you very much

Regards,
Smilie

[eluser]slowgary[/eluser]
If you need to connect to 2 different database servers, you're doing it the right way. If however, you just need to query across multiple databases on the same server, you don't need to make 2 connections - you can simply prepend the database name to your tables.

Here's an example:

Code:

SELECT * FROM my_database.table1
     WHERE my_database.table1.id = 7


SELECT * FROM other_database.table3
     WHERE my_database.table3.id = 5


// this query looks for criminals from america who have
// fled to switzerland
SELECT * FROM switzerland_db.people_table
     WHERE switzerland_db.people_table.id IN (
          SELECT id FROM
               usa.criminals
     )

There's likely a performance hit to making multiple connections to a database server, so this should really be the best way if you're not connecting to 2 different physical servers.