MySQueaL Resources

CACHE INDEX Statement

This MySQL statement tells MySQL to cache the given indexes to a specific index cache, which can be created with the SET GLOBAL statement. This MySQL statement is used only on MyISAM tables.

This MySQL statement tells MySQL to cache the given indexes to a specific index cache, which can be created with the MySQL statement, SET GLOBAL. This MySQL statement is used only on MyISAM tables. Multiple MySQL 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 name of the MySQL table. 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 MySQL 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 MySQL statement, SET GLOBAL 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.