SET @save_key_buffer=@@key_buffer_size; SELECT @@key_buffer_size, @@small.key_buffer_size; @@key_buffer_size @@small.key_buffer_size 2097152 131072 SET @@global.key_buffer_size=16*1024*1024; SET @@global.default.key_buffer_size=16*1024*1024; SET @@global.default.key_buffer_size=16*1024*1024; SET @@global.small.key_buffer_size=1*1024*1024; SET @@global.medium.key_buffer_size=4*1024*1024; SET @@global.medium.key_buffer_size=0; SET @@global.medium.key_buffer_size=0; SHOW VARIABLES like "key_buffer_size"; Variable_name Value key_buffer_size 16777216 SELECT @@key_buffer_size; @@key_buffer_size 16777216 SELECT @@global.key_buffer_size; @@global.key_buffer_size 16777216 SELECT @@global.default.key_buffer_size; @@global.default.key_buffer_size 16777216 SELECT @@global.default.`key_buffer_size`; @@global.default.key_buffer_size 16777216 SELECT @@global.`default`.`key_buffer_size`; @@global.default.key_buffer_size 16777216 SELECT @@`default`.key_buffer_size; @@default.key_buffer_size 16777216 SELECT @@small.key_buffer_size; @@small.key_buffer_size 1048576 SELECT @@medium.key_buffer_size; @@medium.key_buffer_size 0 SET @@global.key_buffer_size=@save_key_buffer; SELECT @@default.key_buffer_size; ERROR 42000: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'default.key_buffer_size' at line 1 SELECT @@skr.table_type="test"; ERROR HY000: Variable 'table_type' is not a variable component (Can't be used as XXXX.variable_name) select @@keycache1.key_cache_block_size; @@keycache1.key_cache_block_size 0 select @@keycache1.key_buffer_size; @@keycache1.key_buffer_size 0 set global keycache1.key_cache_block_size=2048; select @@keycache1.key_buffer_size; @@keycache1.key_buffer_size 0 select @@keycache1.key_cache_block_size; @@keycache1.key_cache_block_size 2048 set global keycache1.key_buffer_size=1*1024*1024; select @@keycache1.key_buffer_size; @@keycache1.key_buffer_size 1048576 select @@keycache1.key_cache_block_size; @@keycache1.key_cache_block_size 2048 set global keycache2.key_buffer_size=4*1024*1024; select @@keycache2.key_buffer_size; @@keycache2.key_buffer_size 4194304 select @@keycache2.key_cache_block_size; @@keycache2.key_cache_block_size 1024 set global keycache1.key_buffer_size=0; select @@keycache1.key_buffer_size; @@keycache1.key_buffer_size 0 select @@keycache1.key_cache_block_size; @@keycache1.key_cache_block_size 0 select @@key_buffer_size; @@key_buffer_size 2097152 select @@key_cache_block_size; @@key_cache_block_size 1024 drop table if exists t1, t2; create table t1 (p int primary key, a char(10)); create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); insert into t1 values (1, 'qqqq'), (11, 'yyyy'); insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), (3, 1, 'yyyy'), (4, 3, 'zzzz'); select * from t1; p a 1 qqqq 11 yyyy select * from t2; p i a 1 1 qqqq 2 1 pppp 3 1 yyyy 4 3 zzzz update t1 set p=2 where p=1; update t2 set i=2 where i=1; cache index t1 keys in keycache1; Table Op Msg_type Msg_text test.t1 assign_to_keycache status OK explain select p from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index select p from t1; p 2 11 explain select i from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL k1 5 NULL 4 Using index select i from t2; i 2 2 2 3 explain select count(*) from t1, t2 where t1.p = t2.i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index 1 SIMPLE t2 ref k1 k1 5 test.t1.p 2 Using where; Using index select count(*) from t1, t2 where t1.p = t2.i; count(*) 3 cache index t2 keys in keycache1; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK update t2 set p=p+1000, i=2 where a='qqqq'; cache index t2 keys in keycache2; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK insert into t2 values (2000, 3, 'yyyy'); cache index t2 keys in keycache1; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK update t2 set p=3000 where a='zzzz'; select * from t2; p i a 1001 2 qqqq 2 2 pppp 3 2 yyyy 3000 3 zzzz 2000 3 yyyy explain select p from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 5 Using index select p from t2; p 2 3 1001 2000 3000 explain select i from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL k1 5 NULL 5 Using index select i from t2; i 2 2 2 3 3 explain select a from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL k2 11 NULL 5 Using index select a from t2; a pppp qqqq yyyy yyyy zzzz select @@keycache2.key_buffer_size; @@keycache2.key_buffer_size 4194304 select @@keycache2.key_cache_block_size; @@keycache2.key_cache_block_size 1024 set global keycache2.key_buffer_size=0; select @@keycache2.key_buffer_size; @@keycache2.key_buffer_size 0 select @@keycache2.key_cache_block_size; @@keycache2.key_cache_block_size 0 update t2 set p=4000 where a='zzzz'; update t1 set p=p+1; set global keycache1.key_buffer_size=0; select * from t2; p i a 1001 2 qqqq 2 2 pppp 3 2 yyyy 4000 3 zzzz 2000 3 yyyy select p from t2; p 2 3 1001 2000 4000 explain select i from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL k1 5 NULL 5 Using index select i from t2; i 2 2 2 3 3 explain select a from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL k2 11 NULL 5 Using index select a from t2; a pppp qqqq yyyy yyyy zzzz select * from t1; p a 3 qqqq 12 yyyy select p from t1; p 3 12