7.4.6.4. Index Preloading
If there are enough blocks in a key cache to hold blocks of an
entire index, or at least the blocks corresponding to its
non-leaf nodes, it makes sense to preload the key cache with
index blocks before starting to use it. Preloading allows you
to put the table index blocks into a key cache buffer in the
most efficient way: by reading the index blocks from disk
sequentially.
Without preloading, the blocks are still placed into the key
cache as needed by queries. Although the blocks will stay in
the cache, because there are enough buffers for all of them,
they are fetched from disk in random order, and not
sequentially.
To preload an index into a cache, use the LOAD INDEX
INTO CACHE statement. For example, the following
statement preloads nodes (index blocks) of indexes of 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 |
+---------+--------------+----------+----------+
The IGNORE LEAVES modifier causes only
blocks for the non-leaf nodes of the index to be preloaded.
Thus, the statement shown preloads all index blocks from
t1, but only blocks for the non-leaf nodes
from t2.
If an index has been assigned to a key cache using a
CACHE INDEX statement, preloading places
index blocks into that cache. Otherwise, the index is loaded
into the default key cache.
