Sample Header Ad - 728x90

Does MySQL/InnoDB use table caches when dealing with foreign tables?

8 votes
1 answer
145 views
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?
Asked by RickMeasham (251 rep)
Aug 16, 2015, 11:56 AM
Last activity: Jul 28, 2025, 10:08 PM