Table of Contents for

MySQL in a Nutshell, 2nd Edition

This statement tells MySQL to cache the given indexes to a specific index cache, which can be created with the SET GLOBAL statement. This statement is used only on MyISAM tables. Multiple tables may be listed in a comma-separated list. To specify only certain indexes of a table, give them in a comma-separated list in parentheses after the table name. The INDEX or KEY keyword may be given for clarity and compatibility with other database products. Note that the naming of specific indexes for a table is ignored in the current versions of MySQL; the option is for a future release. For now, all indexes are assigned to the named cache, which is the same as specifying no indexes.

To create an additional cache, issue a SET GLOBAL statement with the key_buffer_size variable like this:

SET GLOBAL my_cache.key_buffer_size = 100*1024;

CACHE INDEX workreq, clients IN my_cache \G

*************************** 1. row ***************************
   Table: workrequests.workreq
      Op: assign_to_keycache
Msg_type: status
Msg_text: OK
*************************** 2. row ***************************
   Table: workrequests.clients
      Op: assign_to_keycache
Msg_type: status
Msg_text: OK

In this example, the first line creates a cache called my_cache with a buffer size of 100 megabytes. The second line assigns the indexes for the two tables named to my_cache. As long as this cache exists, all queries by all users will use this cache. If you attempt to create a cache index without setting the global variable first, you will receive an error stating that it’s an unknown key cache. If the key cache is eliminated for any reason, the indexes will be assigned back to the default key cache for the server.