Could federated table impact on database performance?

Question

I have some questions before implement the following scenario:

I have the Database A (it contains multiple tables with lots of data, and is being queried by multiple clients) this database contains a users table, which I need to create some triggers, but this database is managed by a partner. We don't have permissions to create triggers.

And the Database B is managed by me, much lighter, the queries are only from one source, and I need to have access to users table data from Database A so I can create triggers and take actions for every update, create or delete in users table from database A.

My most concern is, how can this federated table impact on performance in database A? Database B is not the problem.

Both databases stay in the same geographic location, just different servers.

My goal is to make possible take actions from every transaction in database A users table.

Answer 1

Definitely queries that read federated tables have performance issues.

https://dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html says:

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.

(emphasis mine)

The reason the federated engine was created was to support applications that need to write to tables at a rate greater than a single server can support. If you are inserting to a table and overwhelming the I/O of that server, you can use a federated table so you can write to a table on a different server.

Reading from federated tables is likely to be worse than reading local tables, and cannot be optimized with indexes.

If you need good performance, you should use replication or a CDC tool, to maintain a real table on server B that you can query as a local table, not a federated table.

Another solution would be to cache the user's table in the client application, so you don't have to read it on every query.