Mysql Query across servers without using Federated Table

Question

I moved some tables of my database that had trouble with traffic to another server and don't wanna use federated tables for alot of reasons (performance is the main reason). So I have to create 2 different connections in my PHP class and re-write my queries in the code that have join between tables from different servers.

For example, i have two tables: Users and Enterprise that are in different servers.

When it was on the same server, the query was:

select name from Enterprise E 
inner join Users U on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

So i changed to this:

$rst= select group_concat(U.cod) from Users as U 
where U.codUsers in ($users)

select name from Enterprise E
where E.cod = $mycode and E.cod in ($rst);

My Question is, how can i simillary do this when i have this type of query:

select e.name, e.datebegin, e.dateend from Enterprise E
leftjoin ( select h.callQuantity, h.history from thirdtable 
       inner join Users u on e.cod = u.cod
       where u.codHistory in (1,2,3)
           group by u.cod)

My question is clear? Sorry for my english

Answer 1

First of all, federated tables are the better solution (they are made for exactly that kind of thing you are trying to do by hand). But you don't want them, so here is the next best thing:

For anything more complicated than your first example, you should simulate a remote table manually by inserting the table content instead of the table.

I'll rewrite your first example in that manner, cause your 2nd example is messed up and I don't even know what you want to express there exactly.

You had the following code when using 1 server:

select name from Enterprise E 
inner join Users U on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

You now can replace the table with the actual data of the table:

select name from Enterprise E 
inner join 
   (      select 1 as cod, 4 as codUsers, 20 as codHistory
    union select 2 as cod, 8 as codUsers, 500 as codHistory
    union select 3 as cod, 29 as codUsers, 100 as codHistory
   ) as U
on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

To do that, you have to build the table data as a string (I'm using pdo here):

foreach($db->query('select * from Users U where U.codUsers in ($users)') as $row) {
    if($data !== '') { $data .= 'union '; }
    $data .= 'select ' . $row['cod'] . ' as cod, ' 
             . $row['codUsers'] . ' as codUsers, ' 
             . $row['codHistory'] . ' as codHistory '; 
}

You have to fit it to the layout of your Users-table of course (and don't forget some ' for string-columns), and you can ommit columns you don't need.

You can now place that string anywhere you had your Users-table before and write your code as if it were on 1 server, so your first code would look like

select name from Enterprise E 
inner join ($data) as U on E.cod = U.cod
where E.cod = $my_code and U.codUsers in ($users);

and your second code (although I remind you it is broken to begin with and wouldn't work on 1 server either) would look like

select e.name, e.datebegin, e.dateend from Enterprise E
leftjoin ( select h.callQuantity, h.history from thirdtable 
       inner join ($data) as u on e.cod = u.cod
       where u.codHistory in (1,2,3)
           group by u.cod)

You should make sure to just have a small number of users into your $data-string, then this will work fine. Otherwise, you really need federated tables.

Answer 2

Plan A:

Fetch the data that might be needed from one machine; put the resultset into an associative array.

Do likewise for the second machine. (And third ...)

Play with the two arrays.

Plan B (similar to your first case):

Fetch the data that might be needed from one machine; put the resultset into an associative array.

Build an IN (...); add it to the query for the second machine.

Fetch from second machine.

(etc)

Play with resultsets.

Plan B can be 'optimized' if you know which machine will have the smallest resultset, and starting with it.

PHP has a wealth of array functions, making many of the manipulations easy.