The following items indicate features that the
FEDERATED storage engine does and does not
The remote server must be a MySQL server.
The remote table that a
FEDERATEDtable points to must exist before you try to access the table through the
It is possible for one
FEDERATEDtable to point to another, but you must be careful not to create a loop.
There is no support for transactions.
FEDERATEDtable does not support indexes in the usual sense; because access to the table data is handled remotely, it is actually the remote table that makes use of indexes. This means that, for a query that cannot use any indexes and so requires a full table scan, the server fetches all rows from the remote table and filters them locally. This occurs regardless of any
LIMITused with this
SELECTstatement; these clauses are applied locally to the returned rows.
Queries that fail to use indexes can thus cause poor performance and network overload. In addition, since returned rows must be stored in memory, such a query can also lead to the local server swapping, or even hanging.
Care should be taken when creating a
FEDERATEDtable since the index definition from an equivalent
MyISAMor other table may not be supported. For example, creating a
FEDERATEDtable with an index prefix on
BLOBcolumns will fail. The following definition in
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;
The key prefix in this example is incompatible with the
FEDERATEDengine, and the equivalent statement will fail:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';
If possible, you should try to separate the column and index definition when creating tables on both the remote server and the local server to avoid these index issues.
FEDERATEDstorage engine supports
DELETE, and indexes. It does not support
ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than
DROP TABLE. The current implementation does not use prepared statements.
INSERT ... ON DUPLICATE KEY UPDATEstatements, but if a duplicate-key violation occurs, the statement fails with an error.
Performance on a
FEDERATEDtable when performing bulk inserts (for example, on a
INSERT INTO ... SELECT ...statement) is slower than with other table types because each selected row is treated as an individual
INSERTstatement on the federated table.
Before MySQL 5.0.46, for a multiple-row insert into a
FEDERATEDtable that refers to a remote transactional table, if the insert failed for a row due to constraint failure, the remote table would contain a partial commit (the rows preceding the failed one) instead of rolling back the statement completely. This occurred because the rows were treated as individual inserts.
As of MySQL 5.0.46,
FEDERATEDperforms bulk-insert handling such that multiple rows are sent to the remote table in a batch. This provides a performance improvement. Also, if the remote table is transactional, it enables the remote storage engine to perform statement rollback properly should an error occur. This capability has the following limitations:
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.
Bulk-insert handling does not occur for
INSERT ... ON DUPLICATE KEY UPDATE.
There is no way for the
FEDERATEDengine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database system. The integrity of the data in the local table could be breached if there was any change to the remote database.
DROP TABLEstatement issued against a
FEDERATEDtable drops only the local table, not the remote table.
FEDERATEDtables do not work with the query cache.