From: Pekka Nousiainen Date: July 2 2011 7:09am Subject: bzr push into mysql-5.1-telco-7.0-wl4124-new1 branch (pekka.nousiainen:4406 to 4408) List-Archive: http://lists.mysql.com/commits/140061 Message-Id: <20110702070912.62D225586E@sama.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4408 Pekka Nousiainen 2011-07-02 remove old ndb_statistics.* test files removed: mysql-test/suite/ndb/r/ndb_statistics.result mysql-test/suite/ndb/t/ndb_statistics.test 4407 Pekka Nousiainen 2011-07-02 wl#4124 c02_optim.diff basic index stats test (with explicit enable) added: mysql-test/suite/ndb/r/ndb_index_stat.result mysql-test/suite/ndb/r/ndb_statistics0.result mysql-test/suite/ndb/r/ndb_statistics1.result mysql-test/suite/ndb/t/ndb_index_stat.test mysql-test/suite/ndb/t/ndb_index_stat_enable.inc mysql-test/suite/ndb/t/ndb_statistics.inc mysql-test/suite/ndb/t/ndb_statistics0.test mysql-test/suite/ndb/t/ndb_statistics1.test 4406 Pekka Nousiainen 2011-07-01 [merge] merge telco-7.0 to wl4124-new1 added: mysql-test/suite/ndb/bug12712109.ini mysql-test/suite/ndb_rpl/r/ndb_rpl_circular_2ch_rep_status.result mysql-test/suite/ndb_rpl/r/ndb_rpl_init_rep_status.result mysql-test/suite/ndb_rpl/t/ndb_rpl_circular_2ch_rep_status.cnf mysql-test/suite/ndb_rpl/t/ndb_rpl_circular_2ch_rep_status.test mysql-test/suite/ndb_rpl/t/ndb_rpl_init_rep_status.test sql/ndb_mi.cc sql/ndb_mi.h storage/ndb/compile-cluster storage/ndb/include/kernel/signaldata/GetConfig.hpp storage/ndb/src/common/debugger/signaldata/GetConfig.cpp modified: configure.in mysql-test/suite/funcs_1/r/ndb_views.result mysql-test/suite/ndb/r/ndb_basic.result mysql-test/suite/ndb/r/ndb_config.result mysql-test/suite/ndb/t/ndb_config.test sql/Makefile.am sql/ha_ndb_index_stat.cc sql/ha_ndbcluster.cc sql/ha_ndbcluster.h sql/ha_ndbcluster_binlog.cc storage/ndb/CMakeLists.txt storage/ndb/Makefile.am storage/ndb/include/kernel/GlobalSignalNumbers.h storage/ndb/include/kernel/signaldata/SignalData.hpp storage/ndb/include/mgmapi/mgmapi.h storage/ndb/include/ndb_version.h.in storage/ndb/include/ndbapi/NdbIndexStat.hpp storage/ndb/ndb_configure.m4 storage/ndb/src/common/debugger/signaldata/CMakeLists.txt storage/ndb/src/common/debugger/signaldata/Makefile.am storage/ndb/src/common/debugger/signaldata/SignalDataPrint.cpp storage/ndb/src/common/debugger/signaldata/SignalNames.cpp storage/ndb/src/common/mgmcommon/ConfigRetriever.cpp storage/ndb/src/kernel/blocks/cmvmi/Cmvmi.cpp storage/ndb/src/kernel/blocks/cmvmi/Cmvmi.hpp storage/ndb/src/kernel/blocks/dbtup/Dbtup.hpp storage/ndb/src/kernel/blocks/dbtux/DbtuxStat.cpp storage/ndb/src/kernel/blocks/suma/Suma.cpp storage/ndb/src/kernel/vm/Configuration.cpp storage/ndb/src/kernel/vm/Configuration.hpp storage/ndb/src/mgmapi/mgmapi.cpp storage/ndb/src/mgmapi/mgmapi_internal.h storage/ndb/src/mgmsrv/ConfigInfo.cpp storage/ndb/src/mgmsrv/Defragger.hpp storage/ndb/src/mgmsrv/MgmtSrvr.cpp storage/ndb/src/mgmsrv/MgmtSrvr.hpp storage/ndb/src/mgmsrv/Services.cpp storage/ndb/src/ndbapi/NdbIndexStatImpl.cpp storage/ndb/src/ndbapi/NdbIndexStatImpl.hpp storage/ndb/src/ndbapi/NdbQueryBuilder.cpp storage/ndb/src/ndbapi/NdbQueryBuilder.hpp storage/ndb/src/ndbapi/NdbQueryBuilderImpl.hpp storage/ndb/src/ndbapi/NdbQueryOperation.cpp storage/ndb/src/ndbapi/NdbTransaction.cpp storage/ndb/test/ndbapi/testMgm.cpp storage/ndb/test/ndbapi/testMgmd.cpp storage/ndb/test/ndbapi/testScan.cpp storage/ndb/test/run-test/daily-basic-tests.txt storage/ndb/tools/ndb_config.cpp === added file 'mysql-test/suite/ndb/r/ndb_index_stat.result' --- a/mysql-test/suite/ndb/r/ndb_index_stat.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_index_stat.result 2011-07-02 07:05:32 +0000 @@ -0,0 +1,257 @@ +DROP TABLE IF EXISTS t1, t2; +set @is_enable_default = @@global.ndb_index_stat_enable; +set @is_enable = 1; +set @is_enable = NULL; +# is_enable_on=1 is_enable_off=0 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +set @@global.ndb_index_stat_enable = 1; +set @@local.ndb_index_stat_enable = 1; +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show global variables like 'ndb_index_stat_option'; +Variable_name Value +ndb_index_stat_option loop_checkon=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=32,check_delay=1m,delete_batch=8,clean_delay=0,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90 +set @save_option = @@global.ndb_index_stat_option; +set @@global.ndb_index_stat_option = 'loop_idle=3333,cache_limit=44M'; +set @@global.ndb_index_stat_option = 'cache_lowpct=85,evict_delay=55'; +set @@global.ndb_index_stat_option = 'check_delay=234s'; +show global variables like 'ndb_index_stat_option'; +Variable_name Value +ndb_index_stat_option loop_checkon=1000ms,loop_idle=3333ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=32,check_delay=234s,delete_batch=8,clean_delay=0,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=55s,cache_limit=44M,cache_lowpct=85 +set @@global.ndb_index_stat_option = @save_option; +show global variables like 'ndb_index_stat_option'; +Variable_name Value +ndb_index_stat_option loop_checkon=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=32,check_delay=1m,delete_batch=8,clean_delay=0,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90 +create table t1 ( +a1 int unsigned not null, +b1 int unsigned not null, +c1 int unsigned not null, +primary key (a1), +index b1x (b1), +index c1x (c1) +) engine=ndb; +create table t2 ( +a2 int unsigned not null, +b2 int unsigned not null, +c2 int unsigned not null, +primary key (a2), +index b2x (b2), +index c2x (c2) +) engine=ndb; +analyze table t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +# must use b1x +explain select * from t1 +where b1 = 5 and c1 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref b1x,c1x b1x 4 const # Using where with pushed condition +# must use c2x +explain select * from t2 +where b2 = 5 and c2 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref b2x,c2x c2x 4 const # Using where with pushed condition +# must use b1x, c2x +explain select * from t1, t2 +where c1 = c2 and b1 = 5 and b2 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref b1x,c1x b1x 4 const # +1 SIMPLE t2 ref b2x,c2x c2x 4 test.t1.c1 # Using where with pushed condition +# must use c2x, b1x +explain select * from t1, t2 +where b1 = b2 and c1 = 5 and c2 = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref b2x,c2x c2x 4 const # +1 SIMPLE t1 ref b1x,c1x b1x 4 test.t2.b2 # Using where with pushed condition +# must use t1, c2x +explain select * from t1, t2 +where c1 = c2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL c1x NULL NULL NULL # +1 SIMPLE t2 ref c2x c2x 4 test.t1.c1 # +# must use t2, b1x +explain select * from t1, t2 +where b1 = b2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL b2x NULL NULL NULL # +1 SIMPLE t1 ref b1x b1x 4 test.t2.b2 # +# should NOT say: Using index for group-by +explain select distinct (a1) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # +# must say: Using index for group by +explain select distinct (b1) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL b1x 4 NULL # +# must say: Using index for group by +explain select distinct (c1) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c1x 4 NULL # +drop table t1, t2; +create table t1 (a int, b int, c varchar(10) not null, +primary key using hash (a), index(b,c)) engine=ndb; +insert into t1 values +(1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'), +(4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'), +(7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc'); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set @@local.ndb_index_stat_enable = 0; +select count(*) from t1 where b < 10; +count(*) +0 +select count(*) from t1 where b >= 10 and c >= 'bbb'; +count(*) +6 +select count(*) from t1 where b > 10; +count(*) +6 +select count(*) from t1 where b <= 20 and c < 'ccc'; +count(*) +4 +select count(*) from t1 where b = 20 and c = 'ccc'; +count(*) +1 +select count(*) from t1 where b > 20; +count(*) +3 +select count(*) from t1 where b = 30 and c > 'aaa'; +count(*) +2 +select count(*) from t1 where b <= 20; +count(*) +6 +select count(*) from t1 where b >= 20 and c > 'aaa'; +count(*) +4 +set @@local.ndb_index_stat_enable = 1; +select count(*) from t1 where b < 10; +count(*) +0 +select count(*) from t1 where b >= 10 and c >= 'bbb'; +count(*) +6 +select count(*) from t1 where b > 10; +count(*) +6 +select count(*) from t1 where b <= 20 and c < 'ccc'; +count(*) +4 +select count(*) from t1 where b = 20 and c = 'ccc'; +count(*) +1 +select count(*) from t1 where b > 20; +count(*) +3 +select count(*) from t1 where b = 30 and c > 'aaa'; +count(*) +2 +select count(*) from t1 where b <= 20; +count(*) +6 +select count(*) from t1 where b >= 20 and c > 'aaa'; +count(*) +4 +set @@local.ndb_index_stat_enable = 0; +select count(*) from t1 where b < 10; +count(*) +0 +select count(*) from t1 where b >= 10 and c >= 'bbb'; +count(*) +6 +select count(*) from t1 where b > 10; +count(*) +6 +select count(*) from t1 where b <= 20 and c < 'ccc'; +count(*) +4 +select count(*) from t1 where b = 20 and c = 'ccc'; +count(*) +1 +select count(*) from t1 where b > 20; +count(*) +3 +select count(*) from t1 where b = 30 and c > 'aaa'; +count(*) +2 +select count(*) from t1 where b <= 20; +count(*) +6 +select count(*) from t1 where b >= 20 and c > 'aaa'; +count(*) +4 +set @@local.ndb_index_stat_enable = 1; +select count(*) from t1 where b < 10; +count(*) +0 +select count(*) from t1 where b >= 10 and c >= 'bbb'; +count(*) +6 +select count(*) from t1 where b > 10; +count(*) +6 +select count(*) from t1 where b <= 20 and c < 'ccc'; +count(*) +4 +select count(*) from t1 where b = 20 and c = 'ccc'; +count(*) +1 +select count(*) from t1 where b > 20; +count(*) +3 +select count(*) from t1 where b = 30 and c > 'aaa'; +count(*) +2 +select count(*) from t1 where b <= 20; +count(*) +6 +select count(*) from t1 where b >= 20 and c > 'aaa'; +count(*) +4 +drop table t1; +create table t1 (a int, b int, primary key using hash (a), index x1 (b)) +engine=ndb; +insert into t1 values (1,11),(2,22),(3,33); +select * from t1 order by a; +a b +1 11 +2 22 +3 33 +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +drop table t1; +set @is_enable = @is_enable_default; +set @is_enable = NULL; +# is_enable_on=0 is_enable_off=1 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +set @@local.ndb_index_stat_enable = 0; +set @@global.ndb_index_stat_enable = 0; +drop table mysql.ndb_index_stat_sample; +drop table mysql.ndb_index_stat_head; +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF === removed file 'mysql-test/suite/ndb/r/ndb_statistics.result' --- a/mysql-test/suite/ndb/r/ndb_statistics.result 2011-02-28 10:42:04 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics.result 1970-01-01 00:00:00 +0000 @@ -1,161 +0,0 @@ -drop table if exists t1, t2, t3, t4; -CREATE TABLE t10( -K INT NOT NULL AUTO_INCREMENT, -I INT, J INT, -PRIMARY KEY(K), -KEY(I,J), -UNIQUE KEY(J,K) -) ENGINE=ndbcluster; -INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); -CREATE TABLE t100 LIKE t10; -INSERT INTO t100(I,J) -SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; -CREATE TABLE t10000 LIKE t10; -INSERT INTO t10000(I,J) -SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y -WHERE X.J<50; -INSERT INTO t10000(I,J) -SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y -WHERE X.J>=50; -ANALYZE TABLE t10,t100,t10000; -Table Op Msg_type Msg_text -test.t10 analyze status OK -test.t100 analyze status OK -test.t10000 analyze status OK -SELECT COUNT(*) FROM t10; -COUNT(*) -10 -SELECT COUNT(*) FROM t100; -COUNT(*) -100 -SELECT COUNT(*) FROM t10000; -COUNT(*) -10000 -EXPLAIN -SELECT * FROM t10000 WHERE k = 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 const PRIMARY PRIMARY 4 const 1 -EXPLAIN -SELECT * FROM t10000 WHERE k >= 42 and k < 10000; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE k < 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE k > 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 AS X JOIN t10000 AS Y -ON Y.I=X.I AND Y.J = X.I; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE X ALL I NULL NULL NULL 10000 -1 SIMPLE Y ref J,I I 10 test.X.I,test.X.I 11 Using where -EXPLAIN -SELECT * FROM t100 WHERE k < 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition -EXPLAIN -SELECT * FROM t100 WHERE k > 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE k < 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE k > 42; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition -EXPLAIN -SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref I I 5 const 200 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref J J 5 const 100 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J = 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref J,I I 10 const,const 4 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref I I 5 const 200 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J > 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J < 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J = 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref J,I I 10 const,const 4 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref J J 5 const 100 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K > 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 50 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K < 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 50 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range PRIMARY,J J 9 NULL 25 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K = 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J <> 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I <> 0 AND J = 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref J,I J 5 const 100 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J <> 1 AND I = 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J = 1 AND I <> 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 ref J,I J 5 const 100 Using where with pushed condition -EXPLAIN -SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition -DROP TABLE t10,t100,t10000; -End of 5.1 tests === added file 'mysql-test/suite/ndb/r/ndb_statistics0.result' --- a/mysql-test/suite/ndb/r/ndb_statistics0.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics0.result 2011-07-02 07:05:32 +0000 @@ -0,0 +1,197 @@ +set @is_enable_default = @@global.ndb_index_stat_enable; +set @is_enable = 0; +set @is_enable = NULL; +# is_enable_on=0 is_enable_off=0 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +drop table if exists t1, t2, t3, t4; +CREATE TABLE t10( +K INT NOT NULL AUTO_INCREMENT, +I INT, J INT, +PRIMARY KEY(K), +KEY(I,J), +UNIQUE KEY(J,K) +) ENGINE=ndbcluster +partition by key (K) partitions 1; +INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); +CREATE TABLE t100 LIKE t10; +INSERT INTO t100(I,J) +SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; +CREATE TABLE t10000 LIKE t10; +INSERT INTO t10000(I,J) +SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y +WHERE X.J<50; +INSERT INTO t10000(I,J) +SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y +WHERE X.J>=50; +ANALYZE TABLE t10,t100,t10000; +Table Op Msg_type Msg_text +test.t10 analyze status OK +test.t100 analyze status OK +test.t10000 analyze status OK +SELECT COUNT(*) FROM t10; +COUNT(*) +10 +SELECT COUNT(*) FROM t100; +COUNT(*) +100 +SELECT COUNT(*) FROM t10000; +COUNT(*) +10000 +EXPLAIN +SELECT * FROM t10000 WHERE k = 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 const PRIMARY PRIMARY 4 const 1 +EXPLAIN +SELECT * FROM t10000 WHERE k >= 42 and k < 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 AS X JOIN t10000 AS Y +ON Y.I=X.I AND Y.J = X.I; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE X ALL I NULL NULL NULL 10000 +1 SIMPLE Y ref J,I I 10 test.X.I,test.X.I 11 Using where +EXPLAIN +SELECT * FROM t100 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition +EXPLAIN +SELECT * FROM t100 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 10 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 1000 Using where with pushed condition +EXPLAIN +SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 5 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 500 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref I I 5 const 200 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I I 10 const,const 4 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref I I 5 const 200 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J < 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I I 10 const,const 4 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K < 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 50 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 25 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J <> 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 150 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 1 AND I <> 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 1500 Using where with pushed condition +DROP TABLE t10,t100,t10000; +End of 5.1 tests +set @is_enable = @is_enable_default; +set @is_enable = NULL; +# is_enable_on=0 is_enable_off=0 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF === added file 'mysql-test/suite/ndb/r/ndb_statistics1.result' --- a/mysql-test/suite/ndb/r/ndb_statistics1.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_statistics1.result 2011-07-02 07:05:32 +0000 @@ -0,0 +1,203 @@ +set @is_enable_default = @@global.ndb_index_stat_enable; +set @is_enable = 1; +set @is_enable = NULL; +# is_enable_on=1 is_enable_off=0 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +set @@global.ndb_index_stat_enable = 1; +set @@local.ndb_index_stat_enable = 1; +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +drop table if exists t1, t2, t3, t4; +CREATE TABLE t10( +K INT NOT NULL AUTO_INCREMENT, +I INT, J INT, +PRIMARY KEY(K), +KEY(I,J), +UNIQUE KEY(J,K) +) ENGINE=ndbcluster +partition by key (K) partitions 1; +INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); +CREATE TABLE t100 LIKE t10; +INSERT INTO t100(I,J) +SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; +CREATE TABLE t10000 LIKE t10; +INSERT INTO t10000(I,J) +SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y +WHERE X.J<50; +INSERT INTO t10000(I,J) +SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y +WHERE X.J>=50; +ANALYZE TABLE t10,t100,t10000; +Table Op Msg_type Msg_text +test.t10 analyze status OK +test.t100 analyze status OK +test.t10000 analyze status OK +SELECT COUNT(*) FROM t10; +COUNT(*) +10 +SELECT COUNT(*) FROM t100; +COUNT(*) +100 +SELECT COUNT(*) FROM t10000; +COUNT(*) +10000 +EXPLAIN +SELECT * FROM t10000 WHERE k = 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 const PRIMARY PRIMARY 4 const 1 +EXPLAIN +SELECT * FROM t10000 WHERE k >= 42 and k < 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 42 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 AS X JOIN t10000 AS Y +ON Y.I=X.I AND Y.J = X.I; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE X ALL I NULL NULL NULL 10000 +1 SIMPLE Y ref J,I I 10 test.X.I,test.X.I 1 Using where +EXPLAIN +SELECT * FROM t100 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 42 Using where with pushed condition +EXPLAIN +SELECT * FROM t100 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 58 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 42 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 9958 Using where with pushed condition +EXPLAIN +SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t100 range PRIMARY PRIMARY 4 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY PRIMARY 4 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref I I 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J J 5 const 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref I I 5 const 100 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 99 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J < 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J J 5 const 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J J 9 NULL 256 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K < 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J PRIMARY 4 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range PRIMARY,J PRIMARY 4 NULL 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J <> 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 9744 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I I 10 NULL 101 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J = 1 AND I <> 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 ref J,I J 5 const 2 Using where with pushed condition +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10000 range J,I J 5 NULL 9744 Using where with pushed condition +DROP TABLE t10,t100,t10000; +End of 5.1 tests +set @is_enable = @is_enable_default; +set @is_enable = NULL; +# is_enable_on=0 is_enable_off=1 +# ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable ON +set @@local.ndb_index_stat_enable = 0; +set @@global.ndb_index_stat_enable = 0; +drop table mysql.ndb_index_stat_sample; +drop table mysql.ndb_index_stat_head; +# ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF +show local variables like 'ndb_index_stat_enable'; +Variable_name Value +ndb_index_stat_enable OFF === added file 'mysql-test/suite/ndb/t/ndb_index_stat.test' --- a/mysql-test/suite/ndb/t/ndb_index_stat.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_index_stat.test 2011-07-02 07:05:32 +0000 @@ -0,0 +1,172 @@ +-- source include/have_ndb.inc + +# Notes on index stats in *.test. +# +# Most tables here have few table rows. Index stats are not very +# useful in such cases but the optimizer seems to use them anyway. +# One reason may be that nested joins is only join method. +# +# In real production index stats are computed daily or weekly. +# But tests here must compute them at once if "explain" is used. +# Thus: insert (or other dml) - analyze table - explain. +# +# Index stats are approximate since only one replica was scanned +# and values are interpolated from samples. MTR however should be +# deterministic. If not use --replace column 9 # (rows). + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +set @is_enable_default = @@global.ndb_index_stat_enable; + +set @is_enable = 1; +source ndb_index_stat_enable.inc; + +# test changing suboptions +show global variables like 'ndb_index_stat_option'; +set @save_option = @@global.ndb_index_stat_option; +# some options +set @@global.ndb_index_stat_option = 'loop_idle=3333,cache_limit=44M'; +set @@global.ndb_index_stat_option = 'cache_lowpct=85,evict_delay=55'; +set @@global.ndb_index_stat_option = 'check_delay=234s'; +show global variables like 'ndb_index_stat_option'; +set @@global.ndb_index_stat_option = @save_option; +show global variables like 'ndb_index_stat_option'; + +# TEST: main +create table t1 ( + a1 int unsigned not null, + b1 int unsigned not null, + c1 int unsigned not null, + primary key (a1), + index b1x (b1), + index c1x (c1) +) engine=ndb; + +create table t2 ( + a2 int unsigned not null, + b2 int unsigned not null, + c2 int unsigned not null, + primary key (a2), + index b2x (b2), + index c2x (c2) +) engine=ndb; + +# enough rows to make index stats more approximate + +--disable_query_log +let $i = 1000; +while ($i) +{ + dec $i; + eval insert into t1 values ($i, $i % 100, $i % 10); +} +let $i = 1000; +while ($i) +{ + dec $i; + eval insert into t2 values ($i, $i % 10, $i % 100); +} +--enable_query_log +analyze table t1, t2; + +# TEST: key equal constant + +--echo # must use b1x +--replace_column 9 # +explain select * from t1 + where b1 = 5 and c1 = 5; + +--echo # must use c2x +--replace_column 9 # +explain select * from t2 + where b2 = 5 and c2 = 5; + +# TEST: keys equal constant in join + +--echo # must use b1x, c2x +--replace_column 9 # +explain select * from t1, t2 + where c1 = c2 and b1 = 5 and b2 = 5; + +--echo # must use c2x, b1x +--replace_column 9 # +explain select * from t1, t2 + where b1 = b2 and c1 = 5 and c2 = 5; + +# TEST: join via keys of different selectivity + +--echo # must use t1, c2x +--replace_column 9 # +explain select * from t1, t2 + where c1 = c2; +--echo # must use t2, b1x +--replace_column 9 # +explain select * from t1, t2 + where b1 = b2; + +# TEST: bug#44760 quick distinct +# QUICK_GROUP_MIN_MAX_SELECT says "Using index for group-by". +# Should happen only for low cardinality index. +# wl4124_todo: result is wrong until HA_KEYREAD_ONLY is set + +--echo # should NOT say: Using index for group-by +--replace_column 9 # +explain select distinct (a1) from t1; + +--echo # must say: Using index for group by +--replace_column 9 # +explain select distinct (b1) from t1; + +--echo # must say: Using index for group by +--replace_column 9 # +explain select distinct (c1) from t1; + +# TEST: end +drop table t1, t2; + +# turn index stats OFF in client (falls back on other methods) +# code snippet moved from ndb_index_ordered.test + +create table t1 (a int, b int, c varchar(10) not null, + primary key using hash (a), index(b,c)) engine=ndb; +insert into t1 values + (1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'), + (4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'), + (7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc'); +analyze table t1; +let $is_loop = 4; +while ($is_loop) +{ + # 4-OFF 3-ON 2-OFF 1-ON + let $is_enable = `select ($is_loop=3 or $is_loop=1)`; + dec $is_loop; + eval set @@local.ndb_index_stat_enable = $is_enable; + +select count(*) from t1 where b < 10; +select count(*) from t1 where b >= 10 and c >= 'bbb'; +select count(*) from t1 where b > 10; +select count(*) from t1 where b <= 20 and c < 'ccc'; +select count(*) from t1 where b = 20 and c = 'ccc'; +select count(*) from t1 where b > 20; +select count(*) from t1 where b = 30 and c > 'aaa'; +select count(*) from t1 where b <= 20; +select count(*) from t1 where b >= 20 and c > 'aaa'; +} +drop table t1; + +# bug#XXXXX +# autocreate=false,enable=1 is now acceptable +# following hangs for error_delay (60s) if "no stats" is counted as error +create table t1 (a int, b int, primary key using hash (a), index x1 (b)) +engine=ndb; +insert into t1 values (1,11),(2,22),(3,33); +# make_join_statistics() -> info() -> ndb_index_stat_set_rpk() +select * from t1 order by a; +# index stat entry would now wait 60s on error list +analyze table t1; +drop table t1; + +set @is_enable = @is_enable_default; +source ndb_index_stat_enable.inc; === added file 'mysql-test/suite/ndb/t/ndb_index_stat_enable.inc' --- a/mysql-test/suite/ndb/t/ndb_index_stat_enable.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_index_stat_enable.inc 2011-07-02 07:05:32 +0000 @@ -0,0 +1,39 @@ +# turn ndb_index_stat_enable ON or OFF +# caller sets @is_enable 0/1 +# based on global variable, local follows global +# do nothing if value is already correct +# setting OFF drops stats tables to avoid MTR diff + +let is_enable_on = `select @is_enable and not @@global.ndb_index_stat_enable`; +let is_enable_off = `select not @is_enable and @@global.ndb_index_stat_enable`; +set @is_enable = NULL; + +--echo # is_enable_on=$is_enable_on is_enable_off=$is_enable_off + +--echo # ndb_index_stat_enable - before +show global variables like 'ndb_index_stat_enable'; +show local variables like 'ndb_index_stat_enable'; + +if ($is_enable_on) +{ + # first global + eval set @@global.ndb_index_stat_enable = 1; + eval set @@local.ndb_index_stat_enable = 1; + + # stats thread creates stats tables +} + +if ($is_enable_off) +{ + # first local + eval set @@local.ndb_index_stat_enable = 0; + eval set @@global.ndb_index_stat_enable = 0; + + # stats thread does not (and must not) drop stats tables + eval drop table mysql.ndb_index_stat_sample; + eval drop table mysql.ndb_index_stat_head; +} + +--echo # ndb_index_stat_enable - after +show global variables like 'ndb_index_stat_enable'; +show local variables like 'ndb_index_stat_enable'; === added file 'mysql-test/suite/ndb/t/ndb_statistics.inc' --- a/mysql-test/suite/ndb/t/ndb_statistics.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics.inc 2011-07-02 07:05:32 +0000 @@ -0,0 +1,145 @@ +-- source include/have_ndb.inc + +--disable_warnings +drop table if exists t1, t2, t3, t4; +--enable_warnings + +CREATE TABLE t10( + K INT NOT NULL AUTO_INCREMENT, + I INT, J INT, + PRIMARY KEY(K), + KEY(I,J), + UNIQUE KEY(J,K) +) ENGINE=ndbcluster + partition by key (K) partitions 1; + +INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); + +CREATE TABLE t100 LIKE t10; +INSERT INTO t100(I,J) + SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; + +CREATE TABLE t10000 LIKE t10; + +# Insert into t10000 in two chunks to not +# exhaust MaxNoOfConcurrentOperations +INSERT INTO t10000(I,J) + SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y + WHERE X.J<50; +INSERT INTO t10000(I,J) + SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y + WHERE X.J>=50; + +ANALYZE TABLE t10,t100,t10000; + +SELECT COUNT(*) FROM t10; +SELECT COUNT(*) FROM t100; +SELECT COUNT(*) FROM t10000; + +# +# Bug #59517: Incorrect detection of single row access in +# ha_ndbcluster::records_in_range() + +# Expect a single row (or const) when PK is excact specified +EXPLAIN +SELECT * FROM t10000 WHERE k = 42; + +# All queries below should *not* return a single row +EXPLAIN +SELECT * FROM t10000 WHERE k >= 42 and k < 10000; +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; + +# +# Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique +# + +# 'REF' join of 'Y' should match >1 rows +EXPLAIN +SELECT * FROM t10000 AS X JOIN t10000 AS Y + ON Y.I=X.I AND Y.J = X.I; + +# +# Bug #11804277: INCORRECT INDEX MAY BE SELECTED DUE TO INSUFFICIENT +# STATISTICS FROM CLUSTER +# + +# Open bounded range should return 10% of #rows in table +EXPLAIN +SELECT * FROM t100 WHERE k < 42; +EXPLAIN +SELECT * FROM t100 WHERE k > 42; +EXPLAIN +SELECT * FROM t10000 WHERE k < 42; +EXPLAIN +SELECT * FROM t10000 WHERE k > 42; + +#Closed bounded range should return 5% of #rows in table +EXPLAIN +SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; +EXPLAIN +SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; + +#EQ-range selectivity depends on +# - key length specified +# - #rows in table. +# - unique/non-unique index +# - min 2% selectivity +# +# Possibly combined with open/closed ranges as +# above which further improves selectivity +# +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; + +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 0; + +EXPLAIN +SELECT * FROM t10000 WHERE I = 0; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J > 1; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J < 1; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J = 1; + +EXPLAIN +SELECT * FROM t10000 WHERE J = 0; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K > 1; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K < 1; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; +EXPLAIN +SELECT * FROM t10000 WHERE J = 0 AND K = 1; + +## Verify selection of 'best' index +## (The one of index I/J being EQ) +EXPLAIN +SELECT * FROM t10000 WHERE I = 0 AND J <> 1; +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J = 1; +EXPLAIN +SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; + +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I = 0; +EXPLAIN +SELECT * FROM t10000 WHERE J = 1 AND I <> 0; +EXPLAIN +SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; + + +DROP TABLE t10,t100,t10000; + +--echo End of 5.1 tests === removed file 'mysql-test/suite/ndb/t/ndb_statistics.test' --- a/mysql-test/suite/ndb/t/ndb_statistics.test 2011-02-28 10:42:04 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics.test 1970-01-01 00:00:00 +0000 @@ -1,144 +0,0 @@ --- source include/have_ndb.inc - ---disable_warnings -drop table if exists t1, t2, t3, t4; ---enable_warnings - -CREATE TABLE t10( - K INT NOT NULL AUTO_INCREMENT, - I INT, J INT, - PRIMARY KEY(K), - KEY(I,J), - UNIQUE KEY(J,K) -) ENGINE=ndbcluster; - -INSERT INTO t10(I,J) VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); - -CREATE TABLE t100 LIKE t10; -INSERT INTO t100(I,J) - SELECT X.J, X.J+(10*Y.J) FROM t10 AS X,t10 AS Y; - -CREATE TABLE t10000 LIKE t10; - -# Insert into t10000 in two chunks to not -# exhaust MaxNoOfConcurrentOperations -INSERT INTO t10000(I,J) - SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y - WHERE X.J<50; -INSERT INTO t10000(I,J) - SELECT X.J, X.J+(100*Y.J) FROM t100 AS X,t100 AS Y - WHERE X.J>=50; - -ANALYZE TABLE t10,t100,t10000; - -SELECT COUNT(*) FROM t10; -SELECT COUNT(*) FROM t100; -SELECT COUNT(*) FROM t10000; - -# -# Bug #59517: Incorrect detection of single row access in -# ha_ndbcluster::records_in_range() - -# Expect a single row (or const) when PK is excact specified -EXPLAIN -SELECT * FROM t10000 WHERE k = 42; - -# All queries below should *not* return a single row -EXPLAIN -SELECT * FROM t10000 WHERE k >= 42 and k < 10000; -EXPLAIN -SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; -EXPLAIN -SELECT * FROM t10000 WHERE k < 42; -EXPLAIN -SELECT * FROM t10000 WHERE k > 42; - -# -# Bug #59519 ::set_rec_per_key() assumes ORDER_INDEX to be unique -# - -# 'REF' join of 'Y' should match >1 rows -EXPLAIN -SELECT * FROM t10000 AS X JOIN t10000 AS Y - ON Y.I=X.I AND Y.J = X.I; - -# -# Bug #11804277: INCORRECT INDEX MAY BE SELECTED DUE TO INSUFFICIENT -# STATISTICS FROM CLUSTER -# - -# Open bounded range should return 10% of #rows in table -EXPLAIN -SELECT * FROM t100 WHERE k < 42; -EXPLAIN -SELECT * FROM t100 WHERE k > 42; -EXPLAIN -SELECT * FROM t10000 WHERE k < 42; -EXPLAIN -SELECT * FROM t10000 WHERE k > 42; - -#Closed bounded range should return 5% of #rows in table -EXPLAIN -SELECT * FROM t100 WHERE k BETWEEN 42 AND 10000; -EXPLAIN -SELECT * FROM t10000 WHERE k BETWEEN 42 AND 10000; - -#EQ-range selectivity depends on -# - key length specified -# - #rows in table. -# - unique/non-unique index -# - min 2% selectivity -# -# Possibly combined with open/closed ranges as -# above which further improves selectivity -# -EXPLAIN -SELECT * FROM t10000 WHERE I = 0; -EXPLAIN -SELECT * FROM t10000 WHERE J = 0; - -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J = 0; - -EXPLAIN -SELECT * FROM t10000 WHERE I = 0; -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J > 1; -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J < 1; -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J BETWEEN 1 AND 10; -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J = 1; - -EXPLAIN -SELECT * FROM t10000 WHERE J = 0; -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K > 1; -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K < 1; -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K BETWEEN 1 AND 10; -EXPLAIN -SELECT * FROM t10000 WHERE J = 0 AND K = 1; - -## Verify selection of 'best' index -## (The one of index I/J being EQ) -EXPLAIN -SELECT * FROM t10000 WHERE I = 0 AND J <> 1; -EXPLAIN -SELECT * FROM t10000 WHERE I <> 0 AND J = 1; -EXPLAIN -SELECT * FROM t10000 WHERE I <> 0 AND J <> 1; - -EXPLAIN -SELECT * FROM t10000 WHERE J <> 1 AND I = 0; -EXPLAIN -SELECT * FROM t10000 WHERE J = 1 AND I <> 0; -EXPLAIN -SELECT * FROM t10000 WHERE J <> 1 AND I <> 0; - - -DROP TABLE t10,t100,t10000; - ---echo End of 5.1 tests === added file 'mysql-test/suite/ndb/t/ndb_statistics0.test' --- a/mysql-test/suite/ndb/t/ndb_statistics0.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics0.test 2011-07-02 07:05:32 +0000 @@ -0,0 +1,11 @@ +# index stats OFF + +set @is_enable_default = @@global.ndb_index_stat_enable; + +set @is_enable = 0; +source ndb_index_stat_enable.inc; + +--source ndb_statistics.inc + +set @is_enable = @is_enable_default; +source ndb_index_stat_enable.inc; === added file 'mysql-test/suite/ndb/t/ndb_statistics1.test' --- a/mysql-test/suite/ndb/t/ndb_statistics1.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_statistics1.test 2011-07-02 07:05:32 +0000 @@ -0,0 +1,11 @@ +# index stats ON + +set @is_enable_default = @@global.ndb_index_stat_enable; + +set @is_enable = 1; +source ndb_index_stat_enable.inc; + +--source ndb_statistics.inc + +set @is_enable = @is_enable_default; +source ndb_index_stat_enable.inc; No bundle (reason: useless for push emails).