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