“CACHE INDEX” and “LOAD INDEX INTO CACHE”

[tom worster]

I asked the following question on SO but there's been no activity on it.

The MySQL documentation implies that you can assign one or more of a table's indexes to a named key buffer (and preload them). The syntax definition in the manual is:

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

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

which seems to say that you could assign just one of a table's indexes to the named key buffer. For example:

SET GLOBAL my_keys.key_buffer_size=512*1048576;
CACHE INDEX my_table KEY (PRIMARY) INTO my_keys;
LOAD INDEX INTO CACHE my_table KEY (PRIMARY);

would load only the PRIMARY index of my_table.

But from what I can tell, it doesn't work like that, at least, not in 5.0.87. Instead, the server appears to load all the table's indexes, effectively ignoring the index list part in parenthesis.

For example, I have a big dictionary table:

CREATE TABLE dict (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(330) NOT NULL,
  UNIQUE KEY (name) );

Now, if I attempt to load just the PRIMARY index, the mysqld's resident size in memory increases by the size of dict.MYI (733 MB in my example) which is much bigger than the size of the PRIMARY index alone (103 MB).