664 lines
20 KiB
Plaintext
664 lines
20 KiB
Plaintext
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=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 ON
|
|
show local variables like 'ndb_index_stat_enable';
|
|
Variable_name Value
|
|
ndb_index_stat_enable ON
|
|
# 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_enable=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0
|
|
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_enable=1000ms,loop_idle=3333ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=234s,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=55s,cache_limit=44M,cache_lowpct=85,zero_total=0
|
|
set @@global.ndb_index_stat_option = @save_option;
|
|
show global variables like 'ndb_index_stat_option';
|
|
Variable_name Value
|
|
ndb_index_stat_option loop_enable=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0
|
|
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 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
|
|
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 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
|
|
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 partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 const # # Using pushed condition (`test`.`t1`.`c1` = 5)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` = 5) and (`test`.`t1`.`b1` = 5))
|
|
# must use c2x
|
|
explain select * from t2
|
|
where b2 = 5 and c2 = 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 const # # Using pushed condition (`test`.`t2`.`b2` = 5)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t2` where ((`test`.`t2`.`c2` = 5) and (`test`.`t2`.`b2` = 5))
|
|
# must use b1x, c2x
|
|
explain select * from t1, t2
|
|
where c1 = c2 and b1 = 5 and b2 = 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 const # # Parent of 2 pushed join@1
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 test.t1.c1 # # Child of 't1' in pushed join@1; Using pushed condition (`test`.`t2`.`b2` = 5)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b2` = 5) and (`test`.`t1`.`b1` = 5) and (`test`.`t2`.`c2` = `test`.`t1`.`c1`))
|
|
# must use c2x, b1x
|
|
explain select * from t1, t2
|
|
where b1 = b2 and c1 = 5 and c2 = 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 const # # Parent of 2 pushed join@1
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 test.t2.b2 # # Child of 't2' in pushed join@1; Using pushed condition (`test`.`t1`.`c1` = 5)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c2` = 5) and (`test`.`t1`.`c1` = 5) and (`test`.`t1`.`b1` = `test`.`t2`.`b2`))
|
|
# must use t1, c2x
|
|
explain select * from t1, t2
|
|
where c1 = c2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL c1x NULL NULL NULL # # Parent of 2 pushed join@1
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref c2x c2x 4 test.t1.c1 # # Child of 't1' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c2` = `test`.`t1`.`c1`)
|
|
# must use t2, b1x
|
|
explain select * from t1, t2
|
|
where b1 = b2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL b2x NULL NULL NULL # # Parent of 2 pushed join@1
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x b1x 4 test.t2.b2 # # Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b1` = `test`.`t2`.`b2`)
|
|
# should NOT say: Using index for group-by
|
|
explain select distinct (a1) from t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL # # NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t1`.`a1` AS `a1` from `test`.`t1`
|
|
# must say: Using index for group by
|
|
explain select distinct (b1) from t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b1x b1x 4 NULL # # NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t1`.`b1` AS `b1` from `test`.`t1`
|
|
# must say: Using index for group by
|
|
explain select distinct (c1) from t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c1x c1x 4 NULL # # NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1`
|
|
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;
|
|
Warnings:
|
|
Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
|
|
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 character set latin1;
|
|
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 partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0 ref PRIMARY,a1 a1 5 const # 100.00 Parent of 2 pushed join@1
|
|
1 SIMPLE t2 p0 ref PRIMARY PRIMARY 4 test.t1.b1 # 100.00 Child of 't1' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a1` = 1) and (`test`.`t2`.`b2` = `test`.`t1`.`b1`))
|
|
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 partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0 ref I I 5 const 256 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`I` = 5)
|
|
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 partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0 ref L L 5 const 256 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`L` = 5)
|
|
SELECT count(*) as Count FROM t1 WHERE L = 5;
|
|
Count
|
|
256
|
|
drop table t1;
|
|
create table t1 (
|
|
a int unsigned not null,
|
|
b char(180) not null,
|
|
primary key using hash (a),
|
|
index (b)
|
|
) engine=ndb charset=binary;
|
|
insert into t1 values (1,'a'),(2,'b'),(3,'c');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
drop table t1;
|
|
use test;
|
|
CREATE TABLE t1 (id int unsigned NOT NULL auto_increment PRIMARY KEY,
|
|
val int unsigned, UNIQUE (val)) ENGINE=NDBCluster;
|
|
INSERT INTO test.t1 values (1,1), (2,2);
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#28714864: Running 'analyze' on table with unsupported index length crashes the data nodes
|
|
#
|
|
#setup
|
|
create table t1(
|
|
a int auto_increment primary key,
|
|
b varchar(3053),
|
|
c varchar(3056),
|
|
d varchar(3072),
|
|
col1 varchar(762) not null,
|
|
col2 varchar(762) not null,
|
|
col3 varchar(762) not null,
|
|
col4 varchar(762) not null
|
|
) engine ndb charset latin1;
|
|
# populate enough rows to generate index stats
|
|
# create index on b - it has the maximum key size supported by ndb index stat
|
|
create index idxb on t1(b);
|
|
# analyze should run fine
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
drop index idxb on t1;
|
|
# create index on c - it has a key size unsupported by ndb index stat
|
|
create index idxc on t1(c);
|
|
# analyze should fail with error 911
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze Error Got error 911 'Index stat scan requested on index with unsupported key size' from NDB
|
|
test.t1 analyze status Operation failed
|
|
drop index idxc on t1;
|
|
# create index on d - it has the maximum key size allowed by mysql(3072)
|
|
# but the key size is unsupported by ndb index stat
|
|
create index idxd on t1(d);
|
|
# analyze should fail with error 911
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze Error Got error 911 'Index stat scan requested on index with unsupported key size' from NDB
|
|
test.t1 analyze status Operation failed
|
|
drop index idxd on t1;
|
|
# create index on col1..col4 - it has the maximum key size supported by ndb index stat
|
|
create index idxcol on t1(col1, col2, col3, col4);
|
|
# analyze should run fine
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
drop index idxcol on t1;
|
|
#cleanup
|
|
drop table t1;
|
|
call mtr.add_suppression("Failed to acquire global schema lock");
|
|
call mtr.add_suppression("Failed to release global schema lock");
|
|
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 ON
|
|
show local variables like 'ndb_index_stat_enable';
|
|
Variable_name Value
|
|
ndb_index_stat_enable ON
|
|
# 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
|