MySQL - LOAD INDEX INTO CACHE Statement

The MySQL CACHE INDEX statements used to assign the indexes of a table to a key cache. This statement is applicable only on the MyISAM tables. You can create a key cache using the SET GLOBAL statement.

The MySQL LOAD INDEX INTO CACHE statement is used to preload the index of a table into the default key cache or the cache assigned to it using the CACHE INDEX statement. This statement works only for the MyISAM tables.

Syntax

Following is the syntax of the MySQL LOAD INDEX INTO CACHE Statement −

LOAD INDEX INTO CACHE
   tbl_name
      [PARTITION (partition_list)]
      [{INDEX|KEY} (index_name[, index_name] ...)]
      [IGNORE LEAVES]

Example

Suppose we have created new database and a table named temp using the CREATE statement as shown below −

CREATE TABLE temp (
ID INT, 
Name VARCHAR(100), 
Age INT, 
City VARCHAR(100)) ENGINE = MyISAM ;

Now, let us insert some records into the temp table −

INSERT INTO temp values
(1, 'Radha', 29, 'Vishakhapatnam'),
(2, 'Dev', 30, 'Hyderabad');

Also assume we have created indices on the above created table −

CREATE INDEX sample_index ON temp (name) USING BTREE;
CREATE INDEX composite_index on temp (ID, Name); 

Now, create a new key cache using the SET GLOBAL statement as −

SET GLOBAL TestCache.key_buffer_size=128*1024;

Let us assign the indexes of the table temp to the key cache TestCache −

CACHE INDEX temp IN TestCache;

Output

Following is the output of the above query −

Following query preloads, the above assigned key cache −

LOAD INDEX INTO CACHE temp IGNORE LEAVES;

Output

After executing the above query, it will produce the following output −