diff options
Diffstat (limited to 'mysql-test/r/key_cache.result')
| -rw-r--r-- | mysql-test/r/key_cache.result | 193 |
1 files changed, 192 insertions, 1 deletions
diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 8ec507b2a3f..acf2aa9641c 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -35,9 +35,200 @@ SELECT @@small.key_buffer_size; 1048576 SELECT @@medium.key_buffer_size; @@medium.key_buffer_size -4194304 +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 +16777216 +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 index k1 k1 5 NULL 4 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 |