List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 27 2000 12:36am
Subject:Re: efficient way to create table on localhost from remotehost
select?
View as plain text  
At 4:16 PM -0700 2000-07-26, dave-mlist@stripped wrote:
>I have two databases, one on localhost and one on remotehost.
>I want to do a simple JOIN between tables on different hosts, but I
>don't think it's possible.  I'm looking for a better way than
>having the client iterate over the tables.

Sorry.  You can't use tables from different hosts in the same query.
You'll have to perform the join using your API's logic.  Or else copy
a table from one host to the other before performing the join.


>
>Localhost has a list of names in a table, and I want to query
>remotehost to lookup ID numbers for each name.  I want localhost to
>end up with a small table of (name => ID) tuples.
>
>Assuming I have a connection to the localhost database in
>$dbh_localhost, and I have a connection to the remotehost database in
>$dbh_remotehost, here is a slow way to do the job:
>
>my $sth_local = $dbh_localhost->prepare ("SELECT name FROM list_of_names");
>my $sth_remote = $dbh_remotehost->prepare ("SELECT id FROM name_id 
>WHERE name=?");
>
>$sth_local->execute;
>my $name;
>$sth_local->bind_columns (\$name);
>while ( $name->fetch ) {
>         $sth_remote->execute ($name);
>         my $id = $sth_remote->fetchrow_array;
>         $dbh_localhost->do ("INSERT INTO name_id values ($name, $id)");
>}
>
>This row-by-row iteration strikes me as the worst possible way to
>accomplish the job.  It creates a different select on remotehost for
>every record in the table on localhost.  Can anyone suggest something
>more straightforward and batch oriented?  My main guiding and
>overriding purpose is to minimize the load on remotehost.  I don't
>care about the load on localhost.
>
>Thanks,
>Dave
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/php/manual.php" before
>posting. To request this thread, e-mail mysql-thread45593@stripped
>
>To unsubscribe, send a message to:
>     <mysql-unsubscribe-paul=snake.net@stripped>
>
>If you have a broken mail client that cannot send a message to the 
>above address(Microsoft Outlook), you can use 
>http://lists.mysql.com/php/unsubscribe.php


-- 
Paul DuBois, paul@stripped
Thread
efficient way to create table on localhost from remotehost select?dave-mlist27 Jul
  • Re: efficient way to create table on localhost from remotehostselect?Paul DuBois27 Jul