When you create a
FEDERATED table, the server
creates a table format file in the database directory. The file
begins with the table name and has an
extension. No other files are created, because the actual data is
in a remote table. This differs from the way that storage engines
for local tables work.
For local database tables, data files are local. For example, if
you create a
MyISAM table named
creates a data file named
users.MYD. A handler
for local tables reads, inserts, deletes, and updates data in
local data files, and rows are stored in a format particular to
the handler. To read rows, the handler must parse data into
columns. To write rows, column values must be converted to the row
format used by the handler and written to the local data file.
With the MySQL
FEDERATED storage engine, there
are no local data files for a table (for example, there is no
.MYD file). Instead, a remote database stores
the data that normally would be in the table. The local server
connects to a remote server, and uses the MySQL client API to
read, delete, update, and insert data in the remote table. For
example, data retrieval is initiated using a
FROM SQL statement.
When a client issues an SQL statement that refers to a
FEDERATED table, the flow of information
between the local server (where the SQL statement is executed) and
the remote server (where the data is physically stored) is as
The storage engine looks through each column that the
FEDERATED table has and constructs an
appropriate SQL statement that refers to the remote table.
The statement is sent to the remote server using the MySQL client API.
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
The local server communicates with the remote server using MySQL
client C API functions. It invokes
mysql_real_query() to send the
statement. To read a result set, it uses
mysql_store_result() and fetches
rows one at a time using