diff options
Diffstat (limited to 'mysql-test/r/table_elim.result')
| -rw-r--r-- | mysql-test/r/table_elim.result | 40 |
1 files changed, 20 insertions, 20 deletions
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 05ec4eed6ba..e546b1a27ed 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -58,7 +58,7 @@ t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a) on t0.a=t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00 -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t0`.`a` = `test`.`t1`.`a`)) where 1 # Elimination with aggregate functions @@ -128,7 +128,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 @@ -136,7 +136,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v1 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index Warnings: @@ -156,7 +156,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 @@ -164,7 +164,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: @@ -194,7 +194,7 @@ t2.pk3=t2.pk1+1 and t2.pk2=t2.pk3+t2.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using where This must use only t1: explain select t1.* from t1 left join t2 on t2.pk2=t1.a and t2.pk1=t2.pk2+1 and @@ -212,7 +212,7 @@ explain select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where explain select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; id select_type table type possible_keys key key_len ref rows Extra @@ -224,7 +224,7 @@ t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) on t2.col=t1.col or t2.col=t1.col; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where explain select t1.*, t2.* from t1 left join @@ -247,12 +247,12 @@ this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) drop table t1,t2; create table t1 (a int primary key); insert into t1 values (1),(2); @@ -262,12 +262,12 @@ this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index this must not use table elimination: explain select t1.* from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using index +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) drop table t1, t2; create table t1 (a char(10) primary key); insert into t1 values ('foo'),('bar'); @@ -276,7 +276,7 @@ insert into t2 values ('foo'),('bar'); explain select t1.* from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 ref a a 3 test.t1.a 2 +1 SIMPLE t2 ref a a 3 test.t1.a 2 Using where drop table t1, t2; # # check UPDATE/DELETE that look like they could be eliminated @@ -322,19 +322,19 @@ id select_type table type possible_keys key key_len ref rows Extra explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 @@ -388,7 +388,7 @@ on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or where t1.d=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where explain select t1.* from @@ -405,7 +405,7 @@ select t1.* from t1 left join t2 on t2.pk=3 or t2.pk= 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select t1.* from t1 left join t2 on t2.pk=3 or t2.pk= 3; @@ -416,5 +416,5 @@ select t1.* from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using MRR drop table t1, t2; |