To create a
FEDERATED table you should follow
Create the table on the remote server. Alternatively, make a note of the table definition of an existing table, perhaps using the
SHOW CREATE TABLEstatement.
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=utf8mb4;
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.
When you create the local table it must have an identical field definition to the remote table.
You can improve the performance of a
FEDERATED table by adding indexes to the
table on the host. The optimization will occur because the query
sent to the remote server will include the contents of the
WHERE clause and will be sent to the remote
server and subsequently executed locally. This reduces the
network traffic that would otherwise request the entire table
from the server for local processing.