From 5a458484eaf7e9474fb155aa36832ebdba78c895 Mon Sep 17 00:00:00 2001 From: "igor@rurik.mysql.com" <> Date: Mon, 30 Jun 2003 11:13:41 -0700 Subject: range.result, range.test: Added inequality predicate to range optimization --- mysql-test/t/range.test | 85 ++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 84 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 31b3ced6cc6..e2e8b2fb5f2 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -3,7 +3,7 @@ # --disable_warnings -drop table if exists t1; +drop table if exists t1, t2; --enable_warnings CREATE TABLE t1 ( @@ -165,4 +165,87 @@ select count(*) from t1 where art = 'J' or art = 'j'; select count(*) from t1 where art = 'j' or art = 'J'; select count(*) from t1 where art = 'j'; select count(*) from t1 where art = 'J'; + drop table t1; + +# +# Problem with optimizing != +# + +create table t1 ( + id int not null auto_increment, + name char(1) not null, + uid int not null, + primary key (id), + index uid_index (uid)); + +create table t2 ( + id int not null auto_increment, + name char(1) not null, + uid int not null, + primary key (id), + index uid_index (uid)); + +insert into t1(id, uid, name) values(1, 0, ' '); +insert into t1(uid, name) values(0, ' '); + +insert into t2(uid, name) select uid, name from t1; +insert into t1(uid, name) select uid, name from t2; +insert into t2(uid, name) select uid, name from t1; +insert into t1(uid, name) select uid, name from t2; +insert into t2(uid, name) select uid, name from t1; +insert into t1(uid, name) select uid, name from t2; +insert into t2(uid, name) select uid, name from t1; +insert into t1(uid, name) select uid, name from t2; +insert into t2(uid, name) select uid, name from t1; +insert into t1(uid, name) select uid, name from t2; +insert into t2(uid, name) select uid, name from t1; +insert into t2(uid, name) select uid, name from t1; +insert into t2(uid, name) select uid, name from t1; +insert into t2(uid, name) select uid, name from t1; +insert into t1(uid, name) select uid, name from t2; + +delete from t2; +insert into t2(uid, name) values + (1, CHAR(64+1)), + (2, CHAR(64+2)), + (3, CHAR(64+3)), + (4, CHAR(64+4)), + (5, CHAR(64+5)), + (6, CHAR(64+6)), + (7, CHAR(64+7)), + (8, CHAR(64+8)), + (9, CHAR(64+9)), + (10, CHAR(64+10)), + (11, CHAR(64+11)), + (12, CHAR(64+12)), + (13, CHAR(64+13)), + (14, CHAR(64+14)), + (15, CHAR(64+15)), + (16, CHAR(64+16)), + (17, CHAR(64+17)), + (18, CHAR(64+18)), + (19, CHAR(64+19)), + (20, CHAR(64+20)), + (21, CHAR(64+21)), + (22, CHAR(64+22)), + (23, CHAR(64+23)), + (24, CHAR(64+24)), + (25, CHAR(64+25)), + (26, CHAR(64+26)); + +insert into t1(uid, name) select uid, name from t2; + +delete from t2; +insert into t2(id, uid, name) select id, uid, name from t1; + +select count(*) from t1; +select count(*) from t2; + +explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; +explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; + +select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0; +select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0; + +drop table t1,t2; -- cgit v1.2.1 From 9306f55d8e62aca4e00dab3408a8395af85fb947 Mon Sep 17 00:00:00 2001 From: "igor@rurik.mysql.com" <> Date: Sat, 2 Aug 2003 02:43:18 -0700 Subject: Many files: Added key cache assignment mi_locking.c: Added key cache assignment: correction my_sys.h: Added key cache variable structure --- mysql-test/t/key_cache.test | 82 ++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 81 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index 2da18b68624..e76183f1f5b 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -1,5 +1,5 @@ # -# Test of key cache +# Test of multiple key caches # SET @save_key_buffer=@@key_buffer_size; @@ -40,3 +40,83 @@ SET @@global.key_buffer_size=@save_key_buffer; SELECT @@default.key_buffer_size; --error 1270 SELECT @@skr.table_type="test"; + +select @@keycache1.key_cache_block_size; +select @@keycache1.key_buffer_size; +set global keycache1.key_cache_block_size=2048; +select @@keycache1.key_buffer_size; +select @@keycache1.key_cache_block_size; +set global keycache1.key_buffer_size=1*1024*1024; +select @@keycache1.key_buffer_size; +select @@keycache1.key_cache_block_size; +set global keycache2.key_buffer_size=4*1024*1024; +select @@keycache2.key_buffer_size; +select @@keycache2.key_cache_block_size; +set global keycache1.key_buffer_size=0; +select @@keycache1.key_buffer_size; +select @@keycache1.key_cache_block_size; +select @@key_buffer_size; +select @@key_cache_block_size; + + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +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; +select * from t2; + +update t1 set p=2 where p=1; +update t2 set i=2 where i=1; + +cache index t1 keys in keycache1; + +explain select p from t1; +select p from t1; +explain select i from t2; +select i from t2; +explain select count(*) from t1, t2 where t1.p = t2.i; +select count(*) from t1, t2 where t1.p = t2.i; + +cache index t2 keys in keycache1; +update t2 set p=p+1000, i=2 where a='qqqq'; +cache index t2 keys in keycache2; +insert into t2 values (2000, 3, 'yyyy'); +cache index t2 keys in keycache1; +update t2 set p=3000 where a='zzzz'; +select * from t2; +explain select p from t2; +select p from t2; +explain select i from t2; +select i from t2; +explain select a from t2; +select a from t2; + +select @@keycache2.key_buffer_size; +select @@keycache2.key_cache_block_size; +set global keycache2.key_buffer_size=0; +select @@keycache2.key_buffer_size; +select @@keycache2.key_cache_block_size; + + +update t2 set p=4000 where a='zzzz'; +update t1 set p=p+1; + +set global keycache1.key_buffer_size=0; +select * from t2; +select p from t2; +explain select i from t2; +select i from t2; +explain select a from t2; +select a from t2; + +select * from t1; +select p from t1; + + -- cgit v1.2.1