MySQL Question

MYSQL FEDERATED tables

"A FEDERATED table 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 WHERE or LIMIT used with this SELECT statement; 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."

16.8.3 FEDERATED Storage Engine Notes and Tips

Can anybody explain me on examples what is means?

What is "query that cannot use any indexes"?

This means that i get full data from remote server in any case or not?

Answer Source

The documentation means to say that if you run a query against a federated table, it generates another query that it runs against the remote base table. If the query that runs on the remote server cannot make use of an index, this forces a table-scan on the remote server, and therefore all the rows of that table are copied across the network.

You might think that the query should filter rows on the remote server before sending them back, but it seems it does not do that. It can filter rows on the remote server only if the filtering can be done on the remote side using an index.

There are very few cases where MySQL's federated storage engine is a good idea to use. I avoid it.