List:General Discussion« Previous MessageNext Message »
From:Steven Staples Date:December 9 2009 8:07pm
Subject:RE: Select from remote server from stored procedure
View as plain text  
So what I am reading, I guess it would be safer to just do it how I am
currently doing it, as it really isn't that slow... it's just duplicating
the data elsewhere (I suppose maybe making this a slave table to the other
server... nah... lots of work there :P)

Thanks, and I did search it before, but I guess my searching keywords were
insufficient ;)


Steven Staples


-----Original Message-----
From: Harrison.Fisk@stripped [mailto:Harrison.Fisk@stripped] 
Sent: December 9, 2009 2:07 PM
To: Johan De Meersman
Cc: Neil Aggarwal; Steven Staples; mysql@stripped
Subject: Re: Select from remote server from stored procedure

Hello Johan,

On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:

> Posted this before, but beware: federated tables do NOT use indices.  
> Every
> select is a full table scan, and if you're talking about a logging  
> table
> that could become very expensive very fast.

This is not entirely true.  If you define an index on the local  
federated table, and it makes sense to use it, then a remote WHERE  
clause will be passed through and hence use the remote index.  Not all  
types of index accesses can be passed through such as this, however  
for a single row lookup on a primary key, it should be fine.

It is still not as fast as local access, but it's not as bad as always  
doing a full table scan remotely.

> On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal  
> <neil@stripped>wrote:
>
>>> Is this possible to do?  To make a connection, inside the
>>> stored procedure
>>> to a completely different machine and access the mysql there?
>>
>> The only way I know to access tables from different servers
>> from a single connection is federated tables:
>> http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
>>
>> Once you do that, you are accessing it like a local table.
>>
>> I hope this helps.
>>
>>       Neil

Regards,

Harrison
-- 
Harrison C. Fisk, MySQL Staff Support Engineer
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.709 / Virus Database: 270.14.97/2550 - Release Date: 12/09/09
02:32:00

Thread
Select from remote server from stored procedureSteven Staples9 Dec
  • RE: Select from remote server from stored procedureNeil Aggarwal9 Dec
    • Re: Select from remote server from stored procedureJohan De Meersman9 Dec
      • Re: Select from remote server from stored procedureHarrison Fisk9 Dec
        • RE: Select from remote server from stored procedureSteven Staples9 Dec
          • RE: Select from remote server from stored procedureNeil Aggarwal9 Dec
            • Use stored proc result set from another stored proc without temptable?Bryan Cantwell9 Dec