Caching of Prepared Statements and Stored Programs | test.t1 | preload_keys | status | OK | | test.t2 | preload_keys | status | OK | +---------+--------------+----------+----------+ The  IGNORE LEAVES  modifier causes only blocks for the nonleaf nodes of the index to be preloaded. Thus, the statement shown preloads all index blocks from  t1 , but only blocks for the nonleaf nodes from t2 . If an index has been assigned to a key cache using a  CACHE INDEX  statement, preloading places index blocks into that cache. Otherwise, the index is loaded into the default key cache. It is possible to specify the size of the block buffers for an individual key cache using the key_cache_block_size  variable. This permits tuning of the performance of I/O operations for index files. The best performance for I/O operations is achieved when the size of read buffers is equal to the size of the native operating system I/O buffers. But setting the size of key nodes equal to the size of the I/O buffer does not always ensure the best overall performance. When reading the big leaf nodes, the server pulls in a lot of unnecessary data, effectively preventing reading other leaf nodes. To control the size of blocks in the  .MYI  index file of  MyISAM  tables, use the  --myisam-block-size option at server startup. A key cache can be restructured at any time by updating its parameter values. For example: If you assign to either the  key_buffer_size  or  key_cache_block_size  key cache component a value that differs from the component’s current value, the server destroys the cache’s old structure and creates a new one based on the new values. If the cache contains any dirty blocks, the server saves them to disk before destroying and re-creating the cache. Restructuring does not occur if you change other key cache parameters. When restructuring a key cache, the server first flushes the contents of any dirty buffers to disk. After that, the cache contents become unavailable. However, restructuring does not block queries that need to use indexes assigned to the cache. Instead, the server directly accesses the table indexes using native file system caching. File system caching is not as efficient as using a key cache, so although queries execute, a slowdown can be anticipated. After the cache has been restructured, it becomes available again for caching indexes assigned to it, and the use of file system caching for the indexes ceases. For certain statements that a client might execute multiple times during a session, the server converts the statement to an internal structure and caches that structure to be used during execution. Caching enables the server to perform more efficiently because it avoids the overhead of reconverting the statement should it be needed again during the session. Conversion and caching occurs for these statements: •Prepared statements, both those processed at the SQL level (using the  PREPARE  statement) and those processed using the binary client/server protocol (using the  mysql_stmt_prepare() C API function). The  max_prepared_stmt_count  system variable controls the total number of statements the server caches. (The sum of the number of prepared statements across all sessions.) 1820 8.10.3 Caching of Prepared Statements and Stored Programs mysql>  SET GLOBAL cold_cache.key_buffer_size=4*1024*1024; 8.10.2.6 Restructuring a Key Cache 8.10.2.5 Key Cache Block Size