Name
CACHE INDEX
Synopsis
CACHE INDEXtable
[[INDEX|KEY] (index
, ...),...
] INcache
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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.