"Linking" tables on different servers

[timmymerch]

I'm wondering if anyone has had any experience and could share any insight on keeping two tables on different database servers synchronized.

I'm trying to keep a products table on a remote server that will serve my online store, and duplicate that table on a local machine for taking orders. I can have the local machine query the remote machine to get the product info, but for stuff like inventory lists this method is too slow. The remote database has to be queried to get the product information for each line item of inventory - could be hundreds of queries on a single php page!

Having tables from two different database servers in one sql statement would solve my problem, but I don't think this is possible with MySQL. I could copy the table locally using cron every hour or so - this would work okay, but ideally I'd like things a bit slicker...

Thanks,

-Tim

[jmor90]

Have you looked into replicating the table from your remote server? That way you would have an exact copy of the product table locally to query for your web page. Only make changes to the remote server product table and read from the local copy. Replication will keep the local table up to date fairly painlessly.