drop table if exists t1, t2, t3;
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.storage_engine="test";
ERROR HY000: Variable 'storage_engine' 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
2048
select @@key_buffer_size;
@@key_buffer_size
2097152
select @@key_cache_block_size;
@@key_cache_block_size
1024
set global keycache1.key_buffer_size=1024*1024;
create table t1 (p int primary key, a char(10)) delay_key_write=1;
create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a));
show status like 'key_blocks_used';
Variable_name	Value
Key_blocks_used	0
show status like 'key_blocks_unused';
Variable_name	Value
Key_blocks_unused	KEY_BLOCKS_UNUSED
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;
show status like 'key_blocks_used';
Variable_name	Value
Key_blocks_used	4
show status like 'key_blocks_unused';
Variable_name	Value
Key_blocks_unused	KEY_BLOCKS_UNUSED
cache index t1 key (`primary`) 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 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 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 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
cache index t1 in unknown_key_cache;
ERROR HY000: Unknown key cache 'unknown_key_cache'
cache index t1 key (unknown_key) in keycache1;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	error	Key column 'unknown_key' doesn't exist in table
test.t1	assign_to_keycache	status	Operation failed
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
1024
set global keycache2.key_buffer_size=1024*1024;
select @@keycache2.key_buffer_size;
@@keycache2.key_buffer_size
1048576
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
create table t3 (like t1);
cache index t3 in small;
Table	Op	Msg_type	Msg_text
test.t3	assign_to_keycache	status	OK
insert into t3 select * from t1;
cache index t3 in keycache2;
Table	Op	Msg_type	Msg_text
test.t3	assign_to_keycache	status	OK
cache index t1,t2 in default;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
test.t2	assign_to_keycache	status	OK
drop table t1,t2,t3;
show status like 'key_blocks_used';
Variable_name	Value
Key_blocks_used	4
show status like 'key_blocks_unused';
Variable_name	Value
Key_blocks_unused	KEY_BLOCKS_UNUSED
set global keycache2.key_buffer_size=0;
set global keycache3.key_buffer_size=100;
set global keycache3.key_buffer_size=0;