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	#	Parent of 2 pushed join@1
1	SIMPLE	t2	ref	b2x,c2x	c2x	4	test.t1.c1	#	Child of 't1' in pushed join@1; 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	#	Parent of 2 pushed join@1
1	SIMPLE	t1	ref	b1x,c1x	b1x	4	test.t2.b2	#	Child of 't2' in pushed join@1; 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	#	Parent of 2 pushed join@1
1	SIMPLE	t2	ref	c2x	c2x	4	test.t1.c1	#	Child of 't1' in pushed join@1
# 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	#	Parent of 2 pushed join@1
1	SIMPLE	t1	ref	b1x	b1x	4	test.t2.b2	#	Child of 't2' in pushed join@1
# 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
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;
create table t1 (
pk int not null,
a tinyint not null,
b tinyint unsigned not null,
c smallint not null,
d smallint unsigned not null,
e mediumint not null,
f mediumint unsigned not null,
g int not null,
h int unsigned not null,
i bigint not null,
j bigint unsigned not null,
k float not null,
l double not null,
m decimal not null,
n decimal unsigned not null,
primary key using hash (pk),
index (a),
index (b),
index (c),
index (d),
index (e),
index (f),
index (g),
index (h),
index (i),
index (j),
index (k),
index (l),
index (m),
index (n)
) engine=ndb;
insert into t1 values
(1,11,11,11,11,11,11,11,11,11,11,11,11,11,11),
(2,22,22,22,22,22,22,22,22,22,22,22,22,22,22),
(3,33,33,33,33,33,33,33,33,33,33,33,33,33,33);
select count(*) from t1 where a > 22;
count(*)
1
select count(*) from t1 where b > 22;
count(*)
1
select count(*) from t1 where c > 22;
count(*)
1
select count(*) from t1 where d > 22;
count(*)
1
select count(*) from t1 where e > 22;
count(*)
1
select count(*) from t1 where f > 22;
count(*)
1
select count(*) from t1 where g > 22;
count(*)
1
select count(*) from t1 where h > 22;
count(*)
1
select count(*) from t1 where i > 22;
count(*)
1
select count(*) from t1 where j > 22;
count(*)
1
select count(*) from t1 where k > 22;
count(*)
1
select count(*) from t1 where l > 22;
count(*)
1
select count(*) from t1 where m > 22;
count(*)
1
select count(*) from t1 where n > 22;
count(*)
1
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select count(*) from t1 where a > 22;
count(*)
1
select count(*) from t1 where b > 22;
count(*)
1
select count(*) from t1 where c > 22;
count(*)
1
select count(*) from t1 where d > 22;
count(*)
1
select count(*) from t1 where e > 22;
count(*)
1
select count(*) from t1 where f > 22;
count(*)
1
select count(*) from t1 where g > 22;
count(*)
1
select count(*) from t1 where h > 22;
count(*)
1
select count(*) from t1 where i > 22;
count(*)
1
select count(*) from t1 where j > 22;
count(*)
1
select count(*) from t1 where k > 22;
count(*)
1
select count(*) from t1 where l > 22;
count(*)
1
select count(*) from t1 where m > 22;
count(*)
1
select count(*) from t1 where n > 22;
count(*)
1
drop table t1;
create table t1 (
pk int not null,
a datetime not null,
b date not null,
c year not null,
d time not null,
e timestamp not null,
primary key using hash (pk),
index (a),
index (b),
index (c),
index (d),
index (e)
) engine=ndb;
insert into t1 values
(1,'1971-01-01 01:01:01','1971-01-01','1971','01:01:01','1971-01-01 01:01:01'),
(2,'1972-02-02 02:02:02','1972-02-02','1972','02:02:02','1972-02-02 02:02:02'),
(3,'1973-03-03 03:03:03','1973-03-03','1973','03:03:03','1973-03-03 03:03:03');
select count(*) from t1 where a > '1972-02-02 02:02:02';
count(*)
1
select count(*) from t1 where b > '1972-02-02';
count(*)
1
select count(*) from t1 where c > '1972';
count(*)
1
select count(*) from t1 where d > '02:02:02';
count(*)
1
select count(*) from t1 where e > '1972-02-02 02:02:02';
count(*)
1
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select count(*) from t1 where a > '1972-02-02 02:02:02';
count(*)
1
select count(*) from t1 where b > '1972-02-02';
count(*)
1
select count(*) from t1 where c > '1972';
count(*)
1
select count(*) from t1 where d > '02:02:02';
count(*)
1
select count(*) from t1 where e > '1972-02-02 02:02:02';
count(*)
1
drop table t1;
create table t1 (
pk int not null,
a char(10) not null,
b varchar(10) not null,
c varchar(1000) not null,
d binary(10) not null,
e varbinary(10) not null,
f varbinary(1000) not null,
primary key using hash (pk),
index (a),
index (b),
index (c),
index (d),
index (e),
index (f)
) engine=ndb;
insert into t1 values
('1','111','111','111','111','111','111'),
('2','222','222','222','222','222','222'),
('3','333','333','333','333','333','333');
select count(*) from t1 where a > '222';
count(*)
1
select count(*) from t1 where b > '222';
count(*)
1
select count(*) from t1 where c > '222';
count(*)
1
select count(*) from t1 where d > '222';
count(*)
2
select count(*) from t1 where e > '222';
count(*)
1
select count(*) from t1 where f > '222';
count(*)
1
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select count(*) from t1 where a > '222';
count(*)
1
select count(*) from t1 where b > '222';
count(*)
1
select count(*) from t1 where c > '222';
count(*)
1
select count(*) from t1 where d > '222';
count(*)
2
select count(*) from t1 where e > '222';
count(*)
1
select count(*) from t1 where f > '222';
count(*)
1
drop table t1;
create table t1 (a1 int, b1 int, primary key(b1), key(a1)) engine=ndbcluster partition by key() partitions 1;
create table t2 (b2 int, c2 int, primary key(b2,c2)) engine=ndbcluster partition by key() partitions 1;
# table t1 is only for forcing record by key count for table t2 that should be near 50 (not 1)
analyze table t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
explain select * from t1, t2 where b2 = b1 and a1 = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	PRIMARY,a1	a1	5	const	2	#
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.b1	50	#
drop table t1, t2;
CREATE TABLE t1(
K INT NOT NULL AUTO_INCREMENT,
I INT,
J INT,
L INT,
PRIMARY KEY(K),
KEY(I,J),
KEY(L)
) ENGINE=ndbcluster
partition by key (K) partitions 1;
INSERT INTO t1(I,J,L) VALUES
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0);
INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1;
select i, count(*) from t1 group by 1 order by 1;
i	count(*)
0	256
1	256
2	256
3	256
4	256
5	256
6	256
7	256
8	256
9	256
select l, count(*) from t1 group by 1 order by 1;
l	count(*)
0	256
1	256
2	256
3	256
4	256
5	256
6	256
7	256
8	256
9	256
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain SELECT count(*) as Count FROM t1 WHERE I = 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	I	I	5	const	256	
SELECT count(*) as Count FROM t1 WHERE I = 5;
Count
256
explain SELECT count(*) as Count FROM t1 WHERE L = 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	L	L	5	const	256	
SELECT count(*) as Count FROM t1 WHERE L = 5;
Count
256
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
