diff options
Diffstat (limited to 'mysql-test/r/order_by.result')
| -rw-r--r-- | mysql-test/r/order_by.result | 31 |
1 files changed, 18 insertions, 13 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 51c8f0cc741..c92e3696c21 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -514,7 +514,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using where +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t1.skr 1 Using index condition drop table t1,t2,t3; CREATE TABLE t1 ( `titre` char(80) NOT NULL default '', @@ -607,9 +607,14 @@ FieldKey LongVal StringVal 1 0 2 1 1 3 1 2 1 -EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; +DS-MRR: use two IGNORE INDEX queries, otherwise we get cost races, because +DS-MRR: records_in_range/read_time return the same numbers for all three indexes +EXPLAIN SELECT * FROM t1 IGNORE INDEX (LongField, StringField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField FieldKey 38 NULL 4 Using where; Using filesort +1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Using MRR; Using filesort +EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 @@ -638,7 +643,7 @@ create table t1(a int, b int, index(b)); insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -647,7 +652,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -1004,7 +1009,7 @@ t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where SELECT t2.b as c FROM t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) ORDER BY c; @@ -1107,7 +1112,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range k2 k2 5 NULL 386 Using where; Using filesort +1 SIMPLE t2 range k2 k2 5 NULL 386 Using index condition; Using where; Using MRR; Using filesort SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; id c3 6 14 @@ -1484,8 +1489,8 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using where; Using temporary; Using filesort -1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using where +1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using temporary; Using filesort +1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; @@ -1602,19 +1607,19 @@ INSERT INTO t2 SELECT a+4, b FROM t2; EXPLAIN SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer # should have "using filesort" EXPLAIN SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer # should have "using filesort" EXPLAIN SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t1 range a a 5 NULL 2 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer DROP TABLE t1, t2; # |