开发者

"CACHE INDEX" and "LOAD INDEX INTO CACHE" in MySQL

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 T开发者_JAVA技巧ABLE 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 buch bigger than the size of the PRIMARY index alone (103 MB).


UPDATE 2011-01-08: The documentation for CACHE INDEX actually provides the answer:

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

If I would have properly read the very documentation I referenced in the OP, none of this would ever have happened.


fsb's answer to his own question for those who missed the update.

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


CACHE INDEX

This restriction does not apply in MySQL 5.7.2 or later

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜