Syntax
------

CACHE INDEX                      
 tbl_index_list [, tbl_index_list] ...
 IN key_cache_name

tbl_index_list:
 tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

Description
-----------

The CACHE INDEX statement assigns table indexes to a specific key cache. It is
used only for MyISAM tables.

A default key cache exists and cannot be destroyed. To create more key caches,
the key_buffer_size server system variable.

The associations between tables indexes and key caches are lost on server
restart. To recreate them automatically, it is necessary to configure caches
in a configuration file and include some CACHE INDEX (and optionally LOAD
INDEX) statements in the init file.

Examples
--------

The following statement assigns indexes from the tables t1, t2, and t3 to the
key cache named hot_cache:

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       |
+---------+--------------------+----------+----------+

Implementation (for MyISAM)
---------------------------

Normally CACHE INDEX should not take a long time to execute. Internally it's
implemented the following way:

* Find the right key cache (under LOCK_global_system_variables)
* Open the table with a TL_READ_NO_INSERT lock.
* Flush the original key cache for the given file (under key cache lock)
* Flush the new key cache for the given file (safety)
* Move the file to the new key cache (under file share lock)

The only possible long operations are getting the locks for the table and
flushing the original key cache, if there were many key blocks for the file in
it.

We plan to also add CACHE INDEX for Aria tables if there is a need for this.

URL: https://mariadb.com/kb/en/cache-index/