Features of the MySQL Foreign Data Wrapper v2.6.0
The key features of the MySQL Foreign Data Wrapper are listed below:
MySQL Foreign Data Wrapper provides the write capability. Users can insert, update and delete data in the remote MySQL tables by inserting, updating and deleting the data locally in the foreign tables. MySQL foreign data wrapper uses the Postgres type casting mechanism to provide opposite type casting between MySQL and Postgres data types.
The first column of MySQL table must have unique/primary key for DML to work.
MySQL_FDW establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept and reused for subsequent queries in the same session.
MySQL Foreign Data Wrapper allows the push-down of
WHERE clause to the foreign server for execution. This fearure optimizes remote queries to reduce the number of rows transferred from foreign servers.
MySQL Foreign Data Wrapper supports the column push-down. As a result, the query brings back only those columns that are a part of the select target list.
MySQL Foreign Data Wrapper supports Prepared Statement. The select queries uses prepared statements instead of simple query protocol.
MySQL Foreign Data Wrapper supports Import Foreign Schema which enables the local host to import table definitions on the EDB Postgres Advanced Server from the MySQL server. The new foreign tables are created with the corresponding column types and same table name as that of remote tables in the existing local schema.
MySQL Foreign Data Wrappper allows the cleanup of foreign tables in a single operation using
DROP EXTENSION command. This feature is specifically useful when a foreign table is set for a temporary purpose. The syntax:
DROP EXTENSION mysql_fdw CASCADE;
For more information, see DROP EXTENSION.
MySQL Foreign Data Wrapper supports join push-down. It pushes the joins between the foreign tables of the same remote MySQL server to that remote MySQL server, thereby enhancing the performance.
- Currently, joins involving only relational and arithmetic operators in join-clauses are pushed down to avoid any potential join failure.
- Only the INNER and LEFT/RIGHT OUTER joins are supported.