Skip navigation links
13.11.2.1 Creating a FEDERATED Table Using CONNECTION »
Section Navigation      [Toggle]

13.11.2. How to Create FEDERATED Tables

[+/-]

13.11.2.1. Creating a FEDERATED Table Using CONNECTION
13.11.2.2. Creating a FEDERATED Table Using CREATE SERVER

To create a FEDERATED table you should follow these steps:

  1. Create the table on the remote server. Alternatively, make a note of the table definition of an existing table, perhaps using the SHOW CREATE TABLE statement.

  2. Create the table on the local server with an identical table definition, but adding the connection information that links the local table to the remote table.

For example, you could create the following table on the remote server: 

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

To create the local table that will be federated to the remote table, there are two options available. You can either create the local table and specify the connection string (containing the server name, login, password) to be used to connect to the remote table using the CONNECTION, or you can use an existing connection that you have previously created using the CREATE SERVER statement.

Important

When you create the local table it must have an identical field definition to the remote table.

Note

You can improve the performance of a FEDERATED table by adding indexes to the table on the host, even though the tables will not actually be created locally. The optimization will occur because the query sent to the remote server will include the contents of the WHERE clause will be sent to the remote server and executed locally. This reduces the network traffic that would otherwise request the entire table from the server for local processing.


User Comments

Posted by Roland Bouman on February 21 2007 1:09pm[Delete] [Edit]

There's a stored procedure available at MySQLForge to create FEDERATED tables:

http://forge.mysql.com/snippets/view.php?id=54

It relies on the information_schema of the remote host, so it will only work if both the local and the remote server are 5.0 or greater

Add your own comment.