How MySQL Table Cache Speeds Up Query Execution

To execute database queries efficiently, MySQL must minimize disk interactions and system-level overhead. This article explains how MySQL utilizes its internal table cache—specifically divided into the table definition cache and the table open cache—to store table structures and file descriptors in memory, thereby eliminating redundant file system operations and drastically accelerating query execution.

When a client sends a query to MySQL, the database engine must access the target tables to retrieve or modify data. Under the hood, this requires reading the table’s schema definition and opening the physical files stored on the host operating system. Because opening files and parsing schemas are resource-intensive tasks that require OS system calls, doing this for every single query would severely bottleneck performance. MySQL solves this by maintaining two primary internal caches: the Table Definition Cache and the Table Open Cache.

The Table Definition Cache (configured by the table_definition_cache system variable) stores the parsed metadata of tables, such as column types, indexes, and table constraints. In MySQL 8.0, this information is drawn from the data dictionary. By keeping these definitions in memory, MySQL avoids the CPU overhead of repeatedly parsing table schemas when different queries or threads access the same tables.

The Table Open Cache (configured by the table_open_cache system variable) handles the actual physical file descriptors of the tables. Because MySQL is a multi-threaded database, multiple client connections may execute queries on the same table simultaneously. Each concurrent thread requires its own independent handle (file descriptor) to the table. The Table Open Cache keeps these file handles open and ready in memory. When a query is executed, MySQL retrieves the already-open file descriptor from the cache instead of requesting the operating system to open the physical file on disk.

The direct result of these caches is a massive reduction in query latency, particularly in high-concurrency environments. Instead of suffering from disk I/O bottlenecks and operating system thread contention caused by constant file open and close operations, MySQL can instantly route queries to the active file handles.

Database administrators can monitor the efficiency of the table cache by checking the Opened_tables status variable. If this counter increases rapidly, it indicates that the table open cache is too small, forcing MySQL to frequently close older table handles to make room for new ones. Increasing the cache sizes allows MySQL to maintain more concurrent table handles in memory, ensuring sustained, high-speed query execution.