MySQL - CACHE INDEX Statement

---

---

MySQL CACHE INDEX 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.

Syntax

Following is the syntax of the MySQL CACHE INDEX statement −

CACHE INDEX {
   tbl_name [{INDEX|KEY} (index_name[, index_name] ...)]
}

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

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;

Following query 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 −

Table	Op	Msg_type	Msg_text
sample.temp	assign_to_keycache	status	OK

Multiple tables

You can also assign indexes from multiple tables to a key cache in a single query.

Example

Assume we have created three new tables as shown below −

CREATE TABLE Test1(ID INT, Name VARCHAR(255)) ENGINE = MyISAM;
CREATE TABLE Test2(ID INT, Name VARCHAR(255)) ENGINE = MyISAM;
CREATE TABLE Test3(ID INT, Name VARCHAR(255)) ENGINE = MyISAM;

Now let us Create indexes on these tables.

CREATE INDEX testIndex1 ON Test1 (ID);
CREATE INDEX testIndex1 ON Test2 (ID); 
CREATE INDEX testIndex3 ON Test3 (ID);

Following query adds the indexes on all the above created table in to an index cache −

CACHE INDEX Test1, Test2, Test3 IN TestCache;

Output

Once the query is executed, it will display the following output −

Table	Op	Msg_type	Msg_text
demo.test1	assign_to_keycache	status	OK
demo.test2	assign_to_keycache	status	OK
demo.test3	assign_to_keycache	status	OK

If the specified keycache doesn’t exist this statement generates an error as −

CACHE INDEX temp IN demo;
ERROR 1284 (HY000): Unknown key cache 'demo'