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, PHP Code: $this->db1 = $this->load->database('tbcidb', true); 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 |
Welcome Guest, Not a member yet? Register Sign In |