Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual 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 nonleaf nodes, it makes sense to preload the key cache with index blocks before starting to use it. Preloading enables 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:

| 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 nonleaf nodes of the index to be preloaded. Thus, the statement shown preloads all index blocks from t1, but only blocks for the nonleaf 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.

Download this Manual
User Comments
  Posted by Hugues de Mazancourt on January 6, 2006
If you get an error message like "Indexes use different block sizes", it can be caused by
- fulltext indexes,
- indexes longer than 256 bytes.

The first one is a known bug; for the second one, try to shorten column length or index a sub-part of the column.

For example, I had table t:
f1 VARCHAR(128),
f2 VARCHAR(128),

The index didn't load into cache.
When I shortened f2 to VARCHAR(64), everything got OK.


Sign Up Login You must be logged in to post a comment.