I have some questions before implement the following scenario:
I have the Database A (it contains multiple tables with lots of data, and being query by multiple clients) this database contains a users table, wich i need to create some triggers, but this database is manage by a partner. We dont have permissions to create triggers.
And the Database B is manage by me, much more lighter, the querys 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 database stays in the same geographic location, just different servers.
My goal is to make possible take actions from every transaction in database A users table.
from StackOverflow question
Federated tables can have an impact on performance, as they introduce additional network latency and overhead. When a query is made against a federated table, the query is sent to the remote server, executed there, and then the results are sent back to the local server. This can add some delay to the query execution time.
However, the impact on performance would depend on the size and complexity of the queries being made, as well as the network speed and latency between the two servers. If the queries are small and simple, and the network connection is fast, the impact on performance may be minimal.
In your scenario, since the Database A is being queried by multiple clients, it may be more heavily loaded and therefore performance could be impacted by the federated table.
One way to mitigate this impact on performance is to use caching, which can store the results of frequently-used queries and reduce the need to query the remote server. Another option would be to replicate the users table to Database B, so that the triggers can be applied on the replicated table.