13.7.6.4 LOAD INDEX INTO CACHE Syntax

LOAD INDEX INTO CACHE
  tbl_index_list [, tbl_index_list] ...

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

The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it has been assigned by an explicit CACHE INDEX statement, or into the default key cache otherwise. LOAD INDEX INTO CACHE is used only for MyISAM tables.

The IGNORE LEAVES modifier causes only blocks for the nonleaf nodes of the index to be preloaded.

The following statement preloads nodes (index blocks) of indexes for the tables t1 and t2:

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

This statement preloads all index blocks from t1. It preloads only blocks for the nonleaf nodes from t2.

The syntax of LOAD INDEX INTO CACHE enables you to specify that only particular indexes from a table should be preloaded. The current implementation preloads all the table's indexes into the cache, so there is no reason to specify anything other than the table name.

LOAD INDEX INTO CACHE ... IGNORE LEAVES fails unless all indexes in a table have the same block size. (Prior to MySQL 5.0.87, it fails even without IGNORE LEAVES.) You can determine index block sizes for a table by using myisamchk -dv and checking the Blocksize column.


User Comments
  Posted by Marco Gergele on April 19, 2005
If you get an error like:
| preload_keys | error | Indexes use different block sizes |
| preload_keys | status | Operation failed |
, then you probably have variable-length columns in one of the indices.

The fix is to find a relevant constant-length part for the indexed columns and to index only that fixed-length part.

I'm not sure if variable-length keys aren't cached at all or if just this preload-command fails.

  Posted by Otheus Shelling on February 12, 2009
The only privilege needed for a user is the SELECT privilege for the given table(s).
Sign Up Login You must be logged in to post a comment.