MySQL Forums :: General :: “CACHE INDEX” and “LOAD INDEX INTO CACHE”


Advanced Search

“CACHE INDEX” and “LOAD INDEX INTO CACHE”
Posted by: tom worster ()
Date: December 28, 2010 09:40AM

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).

Options: ReplyQuote


Subject Written By Posted
“CACHE INDEX” and “LOAD INDEX INTO CACHE” tom worster 12/28/2010 09:40AM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” Shawn Taylor 12/28/2010 03:23PM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” Rick James 12/28/2010 04:41PM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” tom worster 12/29/2010 10:42AM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” Rick James 12/29/2010 01:45PM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” tom worster 12/29/2010 10:52AM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” Shawn Taylor 12/29/2010 01:24PM
Re: “CACHE INDEX” and “LOAD INDEX INTO CACHE” tom worster 12/29/2010 03:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.