How can I cache access to a Federated table?

[AppSynergy Support]

The remote system serving the federated table is very slow.

Can I cache it locally in the MariaDB database and periodically update its contents?

[AppSynergy Support]

Yes! There are two ways to cache a federated table.

#1 AutoCahce

Many of our federated drivers support an AutoCache feature. With this enabled the caching happens automatically. Other drivers do not support this and must be cached using #2 below.

See the federated data source documentation for details about which drivers support which options.

#2 Caching a Federated table with a Scheduled Event

Accessing a large amount of federated data for analysis can be slow, especially where joins and large tables are involved. Often a better solution is to create a local copy of the data at periodic intervals to create a data warehouse of sorts. Although most of our drivers support automatic caching (see #1 above) sometimes creating an actual copy within the database itself is needed for complex joins or other reasons.

You can accomplish this with a Scheduled Event that runs periodically and refreshes the data (see Tools > Scheduled Events...). The code might look something like this:

CREATE OR REPLACE TABLE MyLocalTable_NEW AS SELECT *  FROM MyFederatedTable;

ALTER TABLE MyLocalTable_NEW ADD PRIMARY KEY (Id), ADD INDEX (SomeOtherColumn);

RENAME TABLE MyLocalTable TO MyLocalTable_OLD, MyLocalTable_NEW TO MyLocalTable;

DROP TABLE IF EXISTS MyLocalTable_OLD;