I’ve been hearing for MySQL FEDERATED Tables for a while, today went through them and figure out how amazing they are. In simple terms a MySQL FEDERATED Table is a proxy to a regular table located in another DB server. You will see the table as a local table while it’s being located in another MySQL Database Server.

The remote table could be defined in as any Storage-Engine as you want, you just need to map the columns and set the connection. So in your local DB server you can easily query your remote table as if it’s a local table located in your Database.

If you have a remote table like this :

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;

You can set your Federated table like this :

CREATE TABLE federated_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=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

where the connection is :

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

And according to MySQL Documents here is the procedure that MySQL follows for a query from a Federated table Reference:

1. The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.
2. The statement is sent to the remote server using the MySQL client API.
3. The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set).

If the statement produces a result set, each column is converted to internal storage engine format that the FEDERATED engine expects and can use to display the result to the client that issued the original statement.

You have to keep in mind that indexes should be created in the remote server, also you can not alter the table. Also Federated tables can not support transaction. And as another disadvantage of Federated tables, you have to be careful that “the size of the insert cannot exceed the maximum packet size between servers. If the insert exceeds this size, it is broken into multiple packets and the rollback problem can occur” Reference.

Here you can find very useful information about the MySQL Federated Tables.

MySQL FEDERATED Storage-Engine may not be something you deal with every day and for every project. But for distributed projects it makes a lot of sense to use and consider them. Just knowing the concept opens your eyes to a new way of solving the distributed architectures problem.