13.5.5.1. CACHE INDEX Syntax
CACHE INDEX
tbl_index_list [, tbl_index_list] ...
IN key_cache_name
tbl_index_list:
tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
The CACHE INDEX statement assigns table
indexes to a specific key cache. It is used only for
MyISAM tables.
The following statement assigns indexes from the tables
t1, t2, and
t3 to the key cache named
hot_cache:
mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status | OK |
| test.t2 | assign_to_keycache | status | OK |
| test.t3 | assign_to_keycache | status | OK |
+---------+--------------------+----------+----------+
The syntax of CACHE INDEX enables you to
specify that only particular indexes from a table should be
assigned to the cache. The current implementation assigns all
the table's indexes to the cache, so there is no reason to
specify anything other than the table name.
The key cache referred to in a CACHE INDEX
statement can be created by setting its size with a parameter
setting statement or in the server parameter settings. For
example:
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
Key cache parameters can be accessed as members of a
structured system variable. See
Section 5.2.3.1, “Structured System Variables”.
A key cache must exist before you can assign indexes to it:
mysql> CACHE INDEX t1 IN non_existent_cache;
ERROR 1284 (HY000): Unknown key cache 'non_existent_cache'
By default, table indexes are assigned to the main (default)
key cache created at the server startup. When a key cache is
destroyed, all indexes assigned to it become assigned to the
default key cache again.
Index assignment affects the server globally: If one client
assigns an index to a given cache, this cache is used for all
queries involving the index, no matter which client issues the
queries.
