summaryrefslogtreecommitdiff
path: root/mysql-test/r/order_by.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/order_by.result')
-rw-r--r--mysql-test/r/order_by.result74
1 files changed, 37 insertions, 37 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index e2b05ccec9e..b5b0d457f9a 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -506,25 +506,25 @@ gid sid uid
EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
+1 SIMPLE t3 hash_index PRIMARY #hash#PRIMARY:PRIMARY 2:2 test.t2.uid 6 Using where; Using index; Using join buffer (flat, BNLH join)
+1 SIMPLE t1 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.gid 6 Using index; Using join buffer (incremental, BNLH join)
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
+1 SIMPLE t1 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t3.uid 6 Using where; Using index; Using join buffer (flat, BNLH join)
EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
+1 SIMPLE t1 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.gid 6 Using index; Using join buffer (flat, BNLH join)
+1 SIMPLE t3 hash_index PRIMARY #hash#PRIMARY:PRIMARY 2:2 test.t2.uid 6 Using where; Using index; Using join buffer (incremental, BNLH join)
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.uid 1 Using where; Using index
+1 SIMPLE t1 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t3.uid 6 Using where; Using index; Using join buffer (flat, BNLH join)
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 index condition
+1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 2 test.t1.skr 6 Using where; Using join buffer (flat, BNLH join)
drop table t1,t2,t3;
CREATE TABLE t1 (
`titre` char(80) NOT NULL default '',
@@ -1503,7 +1503,7 @@ 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 index condition; Using where; Using temporary; Using filesort
-1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition
+1 SIMPLE t2 hash_ALL a,b,c #hash#a 40 test.t1.a,const 526 Using where; Using join buffer (flat, BNLH join)
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;
@@ -1514,8 +1514,8 @@ SELECT d FROM t3 AS t1, t2 AS 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 t2 range a,b,c c 5 NULL 420 Using where
-1 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index
+1 SIMPLE t2 ref a,b,c b 5 const 139 Using where; Using temporary; Using filesort
+1 SIMPLE t1 hash_index a #hash#a:a 39:39 test.t2.a,const 991 Using where; Using index; Using join buffer (flat, BNLH join)
SELECT d FROM t3 AS t1, t2 AS 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;
@@ -2556,7 +2556,7 @@ ORDER by c
LIMIT 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where
+1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 Using where; Using join buffer (flat, BNLH join)
DROP TABLE t1, t2;
#
# Bug #707848: WHERE condition with OR + ORDER BY + field substitution
@@ -2569,8 +2569,8 @@ SELECT * FROM t1 r JOIN t1 s ON r.a = s.a
WHERE s.a IN (2,9) OR s.a < 100 AND s.a != 0
ORDER BY 1 LIMIT 10;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE r range PRIMARY PRIMARY 4 NULL 12 100.00 Using where; Using index
-1 SIMPLE s eq_ref PRIMARY PRIMARY 4 test.r.a 1 100.00 Using index
+1 SIMPLE r index PRIMARY PRIMARY 4 NULL 12 100.00 Using where; Using index; Using temporary; Using filesort
+1 SIMPLE s hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.r.a 12 8.33 Using index; Using join buffer (flat, BNLH join)
Warnings:
Note 1003 select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where `test`.`s`.`a` = `test`.`r`.`a` and (`test`.`r`.`a` in (2,9) or `test`.`r`.`a` < 100 and `test`.`r`.`a` <> 0) order by 1 limit 10
SELECT * FROM t1 r JOIN t1 s ON r.a = s.a
@@ -2635,9 +2635,9 @@ SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
LEFT JOIN t3 ON t2.i2 = t3.i3
ORDER BY t1.i1 LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t3 system NULL NULL NULL NULL 0 0.00 Const row not found
-1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 100.00 Using index
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 100.00 Using index
+1 SIMPLE t3 system NULL NULL NULL NULL 0 0.00 Const row not found; Using temporary; Using filesort
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 12 100.00 Using index
+1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.i1 12 8.33 Using index; Using join buffer (flat, BNLH join)
Warnings:
Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`i2` = `test`.`t1`.`i1` order by `test`.`t1`.`i1` limit 5
SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
@@ -2678,28 +2678,28 @@ explain extended
SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
+1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 15 6.67 Using join buffer (flat, BNLH join)
Warnings:
Note 1003 select `test`.`t2`.`b` AS `field1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
+1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 15 6.67 Using join buffer (flat, BNLH join)
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
+1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 15 6.67 Using join buffer (flat, BNLH join)
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
+1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 15 6.67 Using join buffer (flat, BNLH join)
Warnings:
Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` group by `test`.`t2`.`b` order by `test`.`t1`.`b`
drop table t1,t2;
@@ -2900,18 +2900,18 @@ INSERT INTO t2 VALUES
EXPLAIN
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
-1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t2 hash_index i_a #hash#i_a:i_a 5:5 test.t1.a 10 Using index; Using join buffer (flat, BNLH join)
EXPLAIN
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
-1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t2 hash_index i_a #hash#i_a:i_a 5:5 test.t1.a 10 Using index; Using join buffer (flat, BNLH join)
EXPLAIN
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
-1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
+1 SIMPLE t2 hash_index i_a #hash#i_a:i_a 5:5 test.t1.a 10 Using index; Using join buffer (flat, BNLH join)
DROP TABLE t1,t2;
#
# MDEV-4974 memory leak in 5.5.32-MariaDB-1~wheezy-log
@@ -2989,8 +2989,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t3a ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
-1 PRIMARY t3b ref f3_key f3_key 6 test.t3a.f3 1 100.00 Using where; End temporary
+1 PRIMARY t3a ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t3b hash_ALL f3_key #hash#f3_key 6 test.t3a.f3 2 50.00 Using where; End temporary; Using join buffer (incremental, BNLH join)
Warnings:
Note 1003 select concat('foo',`test`.`t2`.`f2`) AS `field` from `test`.`t2` semi join ((`test`.`t3` `t3a` join `test`.`t3` `t3b`)) where `test`.`t3a`.`f3` < 'foo' or `test`.`t3b`.`f3` <> 'foo' order by concat('foo',`test`.`t2`.`f2`)
DROP TABLE t1,t2,t3;
@@ -3076,8 +3076,8 @@ explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index a a 5 NULL 5 Using where
-1 SIMPLE t3 ref a a 5 test.t2.a 1
+1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using temporary; Using filesort
+1 SIMPLE t3 hash_ALL a #hash#a 5 test.t2.a 2000 Using join buffer (flat, BNLH join)
#
# This is Q2 which used to have "Using temporary; using filesort" but
# has the same query plan as Q1:
@@ -3086,8 +3086,8 @@ explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t3.a limit 5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index a a 5 NULL 5 Using where
-1 SIMPLE t3 ref a a 5 test.t2.a 1
+1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using temporary; Using filesort
+1 SIMPLE t3 hash_ALL a #hash#a 5 test.t2.a 2000 Using join buffer (flat, BNLH join)
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 5;
pk a b pk a b
@@ -3111,14 +3111,14 @@ explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 25;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort
-1 SIMPLE t3 ref a a 5 test.t2.a 1
+1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using temporary; Using filesort
+1 SIMPLE t3 hash_ALL a #hash#a 5 test.t2.a 2000 Using join buffer (flat, BNLH join)
explain
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t3.a limit 25;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using filesort
-1 SIMPLE t3 ref a a 5 test.t2.a 1
+1 SIMPLE t2 ALL a NULL NULL NULL 200 Using where; Using temporary; Using filesort
+1 SIMPLE t3 hash_ALL a #hash#a 5 test.t2.a 2000 Using join buffer (flat, BNLH join)
select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b
from t2, t3 where t2.a=t3.a order by t2.a limit 25;
pk a b pk a b
@@ -3247,7 +3247,7 @@ books.scheduled_for_removal=0 )
ORDER BY wings.id;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using temporary; Using filesort
-1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00
+1 PRIMARY wings hash_ALL PRIMARY #hash#PRIMARY 4 test.books.wings_id 2 50.00 Using join buffer (flat, BNLH join)
2 MATERIALIZED books ref library_idx library_idx 4 const 1 100.00 Using where
Warnings:
Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id`
View Lorry Controller Status