CODEWITHSUNDEEP
phpmysqlserver
Rafael Pastor García
Rafael Pastor García

Reputation: 15

access two different databases on different servers in the same query

So far we have a server with 2 databases and a mysql user that accesses any of them. For example

`select * from magento.maintable, erp.maintable`

now our erp is very slow and we want to separate our database on another server, but we have hundreds (almost a thousand) sql queries that have access in the same query to the two databases, for example

`insert into magento.table 
select * from erp.maintable`

or select * from erp.maintable inner join magento.table... and more and more

How can I make everything work the same without changing these queries? but with the databases on different servers

To access the databases I have created a class for each database and through an object I make the queries, insertions, updates and deletions, like this

`    public function exec($query, $result_array = true)
{
    $this->data->connect();
    $result = $this->data->query($query, $result_array);
    $this->data->disconnect();
    return $result;
}`

all help is welcome, the point is to find an optimal way to do this and not have to manually change 1000 sql queries made by another programmer

Upvotes: 2

Views: 1235

Answers (1)

slaakso
slaakso

Reputation: 9080

To access more than one database server in one query, you either have to use FEDERATED database engine or use replication to replicate the ERP-data from another server to the original one.

The use of FEDERATED engine is likely to cause additional performance problems and the replication requires some work to set up.

If the sole reason for the new server is the performance in ERP, you might want to see why the ERP is slow and try to solve that (optimize, move both databases to a new server, etc). When you have both databases on the same server, the query optimizer is able to combine and make efficient use of indexes.

Upvotes: 3

Related Questions