611 lines
30 KiB
Plaintext
611 lines
30 KiB
Plaintext
create table t1_innodb (
|
|
t1 tinyint,
|
|
t1u tinyint unsigned,
|
|
s1 smallint,
|
|
s1u smallint unsigned,
|
|
m1 mediumint,
|
|
m1u mediumint unsigned,
|
|
i1 int,
|
|
i1u int unsigned,
|
|
b1 bigint,
|
|
b1u bigint unsigned,
|
|
f1 float,
|
|
d1 double,
|
|
dec1 decimal(10,4),
|
|
dec2 decimal(10,2)
|
|
) engine=innodb;
|
|
insert into t1_innodb values (1,1,1,1,1,1,1,1,1,1,
|
|
1234.5678, 9876.21,
|
|
1234.5678, 9876.21);
|
|
create table t1 (
|
|
t1 tinyint,
|
|
t1u tinyint unsigned,
|
|
s1 smallint,
|
|
s1u smallint unsigned,
|
|
m1 mediumint,
|
|
m1u mediumint unsigned,
|
|
i1 int,
|
|
i1u int unsigned,
|
|
b1 bigint,
|
|
b1u bigint unsigned,
|
|
f1 float,
|
|
d1 double,
|
|
dec1 decimal(10,4),
|
|
dec2 decimal(10,2)
|
|
) engine=ndbcluster;
|
|
insert into t1 values (1,1,1,1,1,1,1,1,1,1,
|
|
1234.5678, 9876.21,
|
|
1234.5678, 9876.21);
|
|
set @@optimizer_switch='block_nested_loop=off';
|
|
create index ix on t1(t1,t1u);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t1u = t1.t1u;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 2 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t2.t1,test.t1.t1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t1u` = `test`.`t1`.`t1u`) and (`test`.`t3`.`t1` = `test`.`t2`.`t1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t1u = t1.t1u;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1u = t2.t1u and t3.t1 = t1.t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t1u` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t1.t1,test.t2.t1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t1` = `test`.`t1`.`t1`) and (`test`.`t3`.`t1u` = `test`.`t2`.`t1u`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1u = t2.t1u and t3.t1 = t1.t1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(s1,s1u);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.s1 = t2.s1 and t3.s1u = t1.s1u;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 3 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`s1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 6 test.t2.s1,test.t1.s1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`s1u` = `test`.`t1`.`s1u`) and (`test`.`t3`.`s1` = `test`.`t2`.`s1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.s1 = t2.s1 and t3.s1u = t1.s1u;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.s1u = t2.s1u and t3.s1 = t1.s1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`s1u` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 6 test.t1.s1,test.t2.s1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`s1` = `test`.`t1`.`s1`) and (`test`.`t3`.`s1u` = `test`.`t2`.`s1u`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.s1u = t2.s1u and t3.s1 = t1.s1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(m1,m1u);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.m1 = t2.m1 and t3.m1u = t1.m1u;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`m1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t2.m1,test.t1.m1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`m1u` = `test`.`t1`.`m1u`) and (`test`.`t3`.`m1` = `test`.`t2`.`m1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.m1 = t2.m1 and t3.m1u = t1.m1u;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.m1u = t2.m1u and t3.m1 = t1.m1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`m1u` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t1.m1,test.t2.m1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`m1` = `test`.`t1`.`m1`) and (`test`.`t3`.`m1u` = `test`.`t2`.`m1u`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.m1u = t2.m1u and t3.m1 = t1.m1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(i1,i1u);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.i1 = t2.i1 and t3.i1u = t1.i1u;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`i1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t2.i1,test.t1.i1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`i1u` = `test`.`t1`.`i1u`) and (`test`.`t3`.`i1` = `test`.`t2`.`i1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.i1 = t2.i1 and t3.i1u = t1.i1u;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.i1u = t2.i1u and t3.i1 = t1.i1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`i1u` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t1.i1,test.t2.i1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`i1` = `test`.`t1`.`i1`) and (`test`.`t3`.`i1u` = `test`.`t2`.`i1u`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.i1u = t2.i1u and t3.i1 = t1.i1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(b1,b1u);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.b1 = t2.b1 and t3.b1u = t1.b1u;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 9 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`b1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 18 test.t2.b1,test.t1.b1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`b1u` = `test`.`t1`.`b1u`) and (`test`.`t3`.`b1` = `test`.`t2`.`b1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.b1 = t2.b1 and t3.b1u = t1.b1u;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.b1u = t2.b1u and t3.b1 = t1.b1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`b1u` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 18 test.t1.b1,test.t2.b1u 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`b1` = `test`.`t1`.`b1`) and (`test`.`t3`.`b1u` = `test`.`t2`.`b1u`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.b1u = t2.b1u and t3.b1 = t1.b1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(f1,d1);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.f1 = t2.f1 and t3.d1 = t1.d1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`f1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 14 test.t2.f1,test.t1.d1 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`d1` = `test`.`t1`.`d1`) and (`test`.`t3`.`f1` = `test`.`t2`.`f1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.f1 = t2.f1 and t3.d1 = t1.d1;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.d1 = t2.d1 and t3.f1 = t1.f1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`d1` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 14 test.t1.f1,test.t2.d1 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`f1` = `test`.`t1`.`f1`) and (`test`.`t3`.`d1` = `test`.`t2`.`d1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.d1 = t2.d1 and t3.f1 = t1.f1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(dec1,dec2);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dec1 = t2.dec1 and t3.dec2 = t1.dec2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 6 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dec1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t2.dec1,test.t1.dec2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dec2` = `test`.`t1`.`dec2`) and (`test`.`t3`.`dec1` = `test`.`t2`.`dec1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dec1 = t2.dec1 and t3.dec2 = t1.dec2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dec2 = t2.dec2 and t3.dec1 = t1.dec1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dec2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t1.dec1,test.t2.dec2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dec1` = `test`.`t1`.`dec1`) and (`test`.`t3`.`dec2` = `test`.`t2`.`dec2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dec2 = t2.dec2 and t3.dec1 = t1.dec1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
drop table t1;
|
|
drop table t1_innodb;
|
|
create table t1_innodb (
|
|
c1 char,
|
|
c2 char(16),
|
|
vc1 varchar(32),
|
|
vc2 varchar(512)
|
|
) engine=innodb;
|
|
insert into t1_innodb values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
|
|
'A long varchar, not that long really...');
|
|
create table t1 (
|
|
c1 char,
|
|
c2 char(16),
|
|
vc1 varchar(32),
|
|
vc2 varchar(512)
|
|
) engine=ndbcluster;
|
|
insert into t1 values ('a', 'abc....xyz', 'abcdefgd12434545...xyc',
|
|
'A long varchar, not that long really...');
|
|
create index ix on t1(c1,c2);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.c1 = t2.c1 and t3.c2 = t1.c2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`c1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 70 test.t2.c1,test.t1.c2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`c2` = `test`.`t1`.`c2`) and (`test`.`t3`.`c1` = `test`.`t2`.`c1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.c1 = t2.c1 and t3.c2 = t1.c2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.c2 = t2.c2 and t3.c1 = t1.c1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`c2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 70 test.t1.c1,test.t2.c2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`c1`) and (`test`.`t3`.`c2` = `test`.`t2`.`c2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.c2 = t2.c2 and t3.c1 = t1.c1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(vc1,vc2);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.vc1 = t2.vc1 and t3.vc2 = t1.vc2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 131 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`vc1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 2182 test.t2.vc1,test.t1.vc2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`vc2` = `test`.`t1`.`vc2`) and (`test`.`t3`.`vc1` = `test`.`t2`.`vc1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.vc1 = t2.vc1 and t3.vc2 = t1.vc2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.vc2 = t2.vc2 and t3.vc1 = t1.vc1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`vc2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 2182 test.t1.vc1,test.t2.vc2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`vc1` = `test`.`t1`.`vc1`) and (`test`.`t3`.`vc2` = `test`.`t2`.`vc2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.vc2 = t2.vc2 and t3.vc1 = t1.vc1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
drop table t1;
|
|
drop table t1_innodb;
|
|
create table t1_innodb (
|
|
y1 year,
|
|
y2 year(4),
|
|
d1 date,
|
|
d2 date,
|
|
t1 time,
|
|
t2 time(0),
|
|
t3 time(6),
|
|
dt1 datetime,
|
|
dt2 datetime(0),
|
|
dt3 datetime(6),
|
|
ts1 timestamp,
|
|
ts2 timestamp(0),
|
|
ts3 timestamp(6)
|
|
) engine=innodb;
|
|
insert into t1_innodb values (99, 2018, '2018-12-04', '2005-01-26',
|
|
'12:00:00', '11:59:59', '11:59:59.999',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
|
|
);
|
|
create table t1 (
|
|
y1 year,
|
|
y2 year(4),
|
|
d1 date,
|
|
d2 date,
|
|
t1 time,
|
|
t2 time(0),
|
|
t3 time(6),
|
|
dt1 datetime,
|
|
dt2 datetime(0),
|
|
dt3 datetime(6),
|
|
ts1 timestamp,
|
|
ts2 timestamp(0),
|
|
ts3 timestamp(6)
|
|
) engine=ndbcluster;
|
|
insert into t1 values (99, 2018, '2018-12-04', '2005-01-26',
|
|
'12:00:00', '11:59:59', '11:59:59.999',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123',
|
|
'2018-03-04 10:11:12', '2018-03-04 10:11:12', '2018-03-04 10:11:12.555123'
|
|
);
|
|
create index ix on t1(y1,y2);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.y1 = t2.y1 and t3.y2 = t1.y2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 2 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`y1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t2.y1,test.t1.y2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`y2` = `test`.`t1`.`y2`) and (`test`.`t3`.`y1` = `test`.`t2`.`y1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.y1 = t2.y1 and t3.y2 = t1.y2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.y2 = t2.y2 and t3.y1 = t1.y1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`y2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 4 test.t1.y1,test.t2.y2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`y1` = `test`.`t1`.`y1`) and (`test`.`t3`.`y2` = `test`.`t2`.`y2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.y2 = t2.y2 and t3.y1 = t1.y1;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(d1,d2);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.d1 = t2.d1 and t3.d2 = t1.d2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`d1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t2.d1,test.t1.d2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`d2` = `test`.`t1`.`d2`) and (`test`.`t3`.`d1` = `test`.`t2`.`d1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.d1 = t2.d1 and t3.d2 = t1.d2;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(t1,t2,t3);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t1.t2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t2.t1,test.t1.t2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t2` = `test`.`t1`.`t2`) and (`test`.`t3`.`t1` = `test`.`t2`.`t1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t1.t2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t2 = t2.t2 and t3.t1 = t1.t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`t2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 8 test.t1.t1,test.t2.t2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t1` = `test`.`t1`.`t1`) and (`test`.`t3`.`t2` = `test`.`t2`.`t2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t2 = t2.t2 and t3.t1 = t1.t1;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t2.t2 and t3.t3 = t1.t3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 4 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`t1` is not null) and (`test`.`t2`.`t2` is not null)); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 15 test.t2.t1,test.t2.t2,test.t1.t3 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`t3` = `test`.`t1`.`t3`) and (`test`.`t3`.`t2` = `test`.`t2`.`t2`) and (`test`.`t3`.`t1` = `test`.`t2`.`t1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.t1 = t2.t1 and t3.t2 = t2.t2 and t3.t3 = t1.t3;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(dt1,dt2,dt3);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t1.dt2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 6 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dt1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t2.dt1,test.t1.dt2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dt2` = `test`.`t1`.`dt2`) and (`test`.`t3`.`dt1` = `test`.`t2`.`dt1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t1.dt2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt2 = t2.dt2 and t3.dt1 = t1.dt1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`dt2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 12 test.t1.dt1,test.t2.dt2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dt1` = `test`.`t1`.`dt1`) and (`test`.`t3`.`dt2` = `test`.`t2`.`dt2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt2 = t2.dt2 and t3.dt1 = t1.dt1;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t2.dt2 and t3.dt3 = t1.dt3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 6 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`dt1` is not null) and (`test`.`t2`.`dt2` is not null)); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 21 test.t2.dt1,test.t2.dt2,test.t1.dt3 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`dt3` = `test`.`t1`.`dt3`) and (`test`.`t3`.`dt2` = `test`.`t2`.`dt2`) and (`test`.`t3`.`dt1` = `test`.`t2`.`dt1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.dt1 = t2.dt1 and t3.dt2 = t2.dt2 and t3.dt3 = t1.dt3;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
create index ix on t1(ts1,ts2,ts3);
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t1.ts2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`ts1` is not null); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t2.ts1,test.t1.ts2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`ts2` = `test`.`t1`.`ts2`) and (`test`.`t3`.`ts1` = `test`.`t2`.`ts1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t1.ts2;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts2 = t2.ts2 and t3.ts1 = t1.ts1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition (`test`.`t2`.`ts2` is not null)
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 10 test.t1.ts1,test.t2.ts2 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`ts1` = `test`.`t1`.`ts1`) and (`test`.`t3`.`ts2` = `test`.`t2`.`ts2`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts2 = t2.ts2 and t3.ts1 = t1.ts1;
|
|
count(*)
|
|
1
|
|
explain select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t2.ts2 and t3.ts3 = t1.ts3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 range ix ix 5 NULL 2 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t2`.`ts1` is not null) and (`test`.`t2`.`ts2` is not null)); Using MRR
|
|
1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ref ix ix 18 test.t2.ts1,test.t2.ts2,test.t1.ts3 2 100.00 Child of 't2' in pushed join@1
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1_innodb` `t1` straight_join `test`.`t1` `t2` straight_join `test`.`t1` `t3` where ((`test`.`t3`.`ts3` = `test`.`t1`.`ts3`) and (`test`.`t3`.`ts2` = `test`.`t2`.`ts2`) and (`test`.`t3`.`ts1` = `test`.`t2`.`ts1`))
|
|
select count(*)
|
|
from t1_innodb as t1
|
|
straight_join t1 as t2
|
|
straight_join t1 as t3 on t3.ts1 = t2.ts1 and t3.ts2 = t2.ts2 and t3.ts3 = t1.ts3;
|
|
count(*)
|
|
1
|
|
alter table t1 drop index ix;
|
|
drop table t1;
|
|
drop table t1_innodb;
|
|
set @@optimizer_switch='block_nested_loop=default';
|