MySQL in a Nutshell

Name

CACHE INDEX

Synopsis

CACHE INDEX table[[INDEX|KEY] (index,  . . . ), ...] IN cache

This statement tells MySQL to cache the given indexes to a specific index cache, which can be created with a 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, they may be given in a comma-separated list in parentheses after the table name. The INDEX or KEY keyword may be given for clarity and compatibility. Note that the naming of specific indexes for a table is ignored in the current versions of MySQL. This 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, a cache called my_cache is created in the first line 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 the key cache is ...