CodeIgniter Forums
Multiple databases - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Multiple databases (/showthread.php?tid=32443)



Multiple databases - El Forum - 07-23-2010

[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?! Sad
Is there a way to dump which database params are beeing used?

Thanks!
Smilie


Multiple databases - El Forum - 07-23-2010

[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


Multiple databases - El Forum - 07-23-2010

[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.


Multiple databases - El Forum - 07-23-2010

[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


Multiple databases - El Forum - 07-23-2010

[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.


Multiple databases - El Forum - 07-23-2010

[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 Smile

Regards,
Smilie


Multiple databases - El Forum - 08-12-2010

[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.