Join 2 Databases with Different Mysqli Usernames

Hi.

I have a database that is way to large, I built it upon an existing database and now I need to separate the database information so I can reduce my 2.7 Gig MySQL Database.

I am trying to figure out how to go from a single  database to 2 different databases.

I need to join 2 databases but all the examples I have seen seem to suggest that I must have the same username and password for both databases.

Here is my SQL queries...

PHP Code:

$sql = "SELECT tb_user_message.msg_id AS tm_id, 
 tb_user_message.from_user_id AS tm_fromUserId, 
 amtb_user.login AS tm_fromUser, 
 amtb_user.email AS tm_fromEmail, 
 amtb_user.name_f AS tm_fromFirstName, 
 amtb_user.name_l AS tm_fromLastName, 
 tb_user_message.msg_content AS tm_message, 
 tb_user_message.msg_subject AS tm_subject, 
 tb_user_message.msg_regdate AS tm_date 
 FROM tb_user_message ". 
 INNER JOIN amtb_user ".
 ON tb_user_message.from_user_id = amtb_user.user_id 
 WHERE tb_user_message.to_user_id = '".$user_id."'  
 LIMIT ".$offset.", ".$limit; 


In the database config I have defined 2 different groups.

PHP Code:

$this->db1 = $this->load->database('tbcidb', true);
$this->db2 = $this->load->database('amtbdb', true); 


How should I go about structuring complex queries ?   More importantly can it be done ?
Looking at google I found this one.
Example 1 http://stackoverflow.com/questions/49164...odeigniter
The question is exactly like mine but the solution expects the two databases having same user.
Example 2 http://stackoverflow.com/questions/14159...odeigniter
Same issue.
Thanks.

I could be mistaken, but I don't believe the database library can do a join between two databases unless you can access both databases on the same connection (not just the same username and password). I'm not even sure that the underlying database interfaces in PHP would let you do this. You will probably have to perform two separate queries and join the results in your PHP code, rather than having the database (or database library) perform the join for you.

Another alternative, if you are hosting both databases on the same server but separating access by user, would be to setup a user with access to both databases which is only used to handle this specific issue. Then, most of the system would use a connection which only had access to one database or the other, but when a join across databases is needed you could access both. At that point, it's just a matter of ensuring that the SQL is explicit enough to indicate which database hosts which table.

While I don't have experience with MySQL databases that large, I have used much larger databases on other platforms. Unless the database server itself simply can't support the database, or you're running into other issues related to the size of the database, you might want to evaluate other options. Performance issues shouldn't occur just because the database is 3GB. A quick search indicates that MySQL shouldn't be much different from other platforms in this respect: http://stackoverflow.com/questions/1276/...to-degrade

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide