Is it possible to do a cross machine JOIN (i.e. cross DBs that are located on different machines) in mySQL?

Question

I know in SQL Server this is possible using linked servers. Is there some analogous way to do this in mySQL?

Thanks!

Answer 1

You can look into the federated storage engine. I don't know it's stability but it's supposed to support something like this.

Answer 2

Yes, it is possible with the Federated Storage engine, as Olafur Waage states in his answer. This storage engine has to be explicitly invoked (by making the relevant entry in the startup file), but you can set up "shadow" tables locally of the ones you want to query remotely. But beware: the performance may not be that great - in recent tests I did the query executed locally against the remote table (via my federated table) ended up coping the entire contents of the table over the wire and then executing the query, apparently without access to indexes that exist on the remote table (even though they were defined in teh federated table). The result was that a simple query that took about 10 seconds remotely, had to be aborted after about an hour when run against the federated table.