diff options
Diffstat (limited to 'mysql-test/main/derived_split_innodb.result')
| -rw-r--r-- | mysql-test/main/derived_split_innodb.result | 24 |
1 files changed, 12 insertions, 12 deletions
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index be3de7dbc02..c326a895a38 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -21,7 +21,7 @@ WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 -2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort +2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; n1 @@ -128,8 +128,8 @@ left join (v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) on t1.f1=t.f1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t const f2 NULL NULL NULL 1 Impossible ON condition -1 PRIMARY <derived2> const key1 NULL NULL NULL 1 Impossible ON condition +1 PRIMARY t const f2 NULL NULL NULL 0 Impossible ON condition +1 PRIMARY <derived2> const key1 NULL NULL NULL 0 Impossible ON condition 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 2 DERIVED t2 ALL PRIMARY NULL NULL NULL 3 Using temporary; Using filesort set statement optimizer_switch='split_materialized=off' for explain select t.f2 @@ -138,8 +138,8 @@ left join (v1 join t1 as t on v1.f1=t.f1 and t.f2 = null) on t1.f1=t.f1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t const f2 NULL NULL NULL 1 Impossible ON condition -1 PRIMARY <derived3> const key1 NULL NULL NULL 1 Impossible ON condition +1 PRIMARY t const f2 NULL NULL NULL 0 Impossible ON condition +1 PRIMARY <derived3> const key1 NULL NULL NULL 0 Impossible ON condition 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 3 DERIVED t2 index NULL PRIMARY 4 NULL 3 drop view v1; @@ -157,26 +157,26 @@ set statement optimizer_switch='split_materialized=off' for EXPLAIN SELECT * FROM t1 JOIN -(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +(SELECT t1_inner.a, t1_inner.b FROM t1 as t1_inner, t2 as t2_inner WHERE t1_inner.b = t2_inner.c GROUP BY t1_inner.a, t1_inner.b) as dt WHERE t1.a = dt.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index 1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 -3 DERIVED t1 index NULL a_2 10 NULL 6 Using where; Using index -3 DERIVED t2 ref c c 5 test.t1.b 1 Using index +3 DERIVED t1_inner index NULL a_2 10 NULL 6 Using where; Using index +3 DERIVED t2_inner ref c c 5 test.t1_inner.b 1 Using index set statement optimizer_switch='split_materialized=on' for EXPLAIN SELECT * FROM t1 JOIN -(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt +(SELECT t1_inner.a, t1_inner.b FROM t1 as t1_inner, t2 as t2_inner WHERE t1_inner.b = t2_inner.c GROUP BY t1_inner.a, t1_inner.b) as dt WHERE t1.a = dt.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a,a_2 a_2 10 NULL 6 Using where; Using index 1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 -3 DERIVED t1 index a,a_2 a_2 10 NULL 6 Using where; Using index; Using temporary; Using filesort -3 DERIVED t2 ref c c 5 test.t1.b 1 Using index +3 DERIVED t1_inner index a,a_2 a_2 10 NULL 6 Using where; Using index +3 DERIVED t2_inner ref c c 5 test.t1_inner.b 1 Using index DROP TABLE t1, t2; # # Bug mdev-25714: usage non-splitting covering index is cheaper than @@ -210,7 +210,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 1 PRIMARY t1 ref idx idx 4 test.t2.id 1 1 PRIMARY <derived2> ref key0 key0 9 test.t2.id,test.t1.id 2 -2 LATERAL DERIVED t3 ref idx1,idx2 idx2 4 test.t1.itemid 1 Using index condition; Using where +2 DERIVED t3 ref idx1,idx2 idx1 4 const 5 Using where; Using index select t1.id, t1.itemid, dt.id, t2.id from t1, (select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt, |