How to join two tables in different server?

[Eric Kwon]

Hi~ This topic may be not proper for this forum.


I am upgrading the DB structure including schema, server architecture by using application partitioning. The partitioning which I mean is not MySQL partitioning. MySQL partitioning does not support distributed partitioning.

Our site is social networking.

We are preparing multiple mysql cluster(one master, several slaves) per each data partition which keeps only data of related tables by hashing user's unique key.

The number of total partitions(dbs) is 16.

hashing function is simple : mno (user's unique identifier) % total partition number.

We need some function to display mutual friends of two users (one is the owener of personal space, the other is visitor).

The name of my friends table is c_contact_XXX. The data of friends tables are distributed in the related db of multiple mysql clusters.

if owner user A's mno is 1282 and visitor user B's mno is 1372, user A's friends table is c_contact_082 and user B's friend table is c_contact_072.

If these two table reside in same server, the query for obtaining mutual friends between user A and B is as follows.


SELECT c1.u_mno,c1.u_id,c1.u_nick FROM
c_fs2.c_contact_082 as c1 INNER JOIN c_fs12.c_contact_072 as c2 ON c1.u_mno = c2.u_mno WHERE c1.i_mno=1282 AND c2.i_mno=1372

When we partition c_fs databases to different clusters, we need join of two tables which reside in different server.


we can create temporary memory table for holding the result set of one select and join another table in other server with this temporary table.

However, we need to create temporary table for this method.


Under this scenario, is there a neat way to join two tables in different server by which we can avoid the overhead of creating temporary memory table?