TL;DR: Does a table referred to via
schema.table get held in table cache?
I have a large number of client databases that our application connects to based on an http request. These are used for every part of the application except for access logging. We use a second central schema for recording of login attempts across all instances of the application.
The login table, however, is accessed via the connection to the local schema as a foreign table. For example:
UPDATE central_schema.login SET column = 'value';
Rather than using a second connection directly to the central schema and just dealing with
login as a local table:
UPDATE login SET column = 'value';
Or switching schemas:
USE central_schema; UPDATE login SET column = 'value'; USE client_schema;
Given that during busy times I see a lot of simple update or select queries waiting for the table to close is it possible that MySQL doesn't cache tables when referred to cross-schema? Is it possible that, because it's connected to
client_schema, it wants to use the client schema cache and thus the foreign table can't be properly cached?
And, if I can't get a definitive answer to that, how would I even begin to test what's in the cache and what isn't?