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).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0-wl4124-new1 branch (pekka.nousiainen:4406to 4408) | Pekka Nousiainen | 4 Jul |
