Mysql query on servers without using Federated Table

Question

I moved several tables in my database that had traffic problems to another server and did not want to use federated tables for many reasons (the main reason is performance). So I need to create 2 different joins in my PHP class and rewrite my queries in code that connects between tables from different servers.

For example, I have two tables: "Users" and "Enterprise", which are located on different servers.

When it was on the same server, the request 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 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 do this when I have this type of request:

 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) 

Is my question clear? sorry for my English

Answer 1

First of all, integrated tables are the best solution (they are made just for what you are trying to do manually). But you do not want them, so here is the next best thing:

For something more complex than your first example, you must simulate the remote table manually by inserting the contents of the table instead of the table.

I will rewrite your first example this way, because your second example is corrupted, and I don’t even know what you want to express exactly there.

When using 1 server, you had the following code:

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

Now you can replace the table with actual table data:

 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 this, you need to collect the table data as a string (I use 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 should put it in the layout of your user table, of course (and don't forget some "for row columns"), and you can omit columns that you don't need.

Now you can place this line anywhere where your Users table used to be and write your code as if it were on 1 server, so your first code will 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 that it does not work and does not work on 1 server) will 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 have to make sure that you have a small number of users in your $ data line, then this will work fine. Otherwise, you will need join tables.

Answer 2

Plan A:

Get data that may be required from one machine; Put the result set in an associative array.

Similarly for the second car. (And the third ...)

Play with two arrays.

Plan B (similar to your first case):

Get data that may be required from one machine; Put the result set in an associative array.

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

Remove from second machine.

(etc.)

Playback using result sets.

Plan B can be “optimized” if you know which of the machines will have the smallest set of results and starting with it.

PHP has many array functions, which makes many manipulations easier.