bug #27531: 5.1 part of the fix:

 - added join cache indication in EXPLAIN (Extra column).
 - prefer filesort over full scan over 
   index for ORDER BY (because it's faster).
 - when switching from REF to RANGE because
   RANGE uses longer key turn off sort on
   the head table only as the resulting 
   RANGE access is a candidate for join cache
   and we don't want to disable it by sorting
   on the first table only. 

1 files changed, 10 insertions, 10 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 6ffb769292f..1d6ef6fee5c 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -607,11 +607,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	index	period	period	4	NULL	41810	
+1	SIMPLE	t3	ALL	period	NULL	NULL	NULL	41810	Using filesort
 1	SIMPLE	t1	ref	period	period	4	test.t3.period	4181	
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	period	period	4	NULL	41810	
+1	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using filesort
 1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	
 select period from t1;
 period
@@ -1430,7 +1430,7 @@ companynr	companynr
 41	40
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
+1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary; Using join cache
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period
@@ -2362,7 +2362,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2);
 insert into t2 values (1,3), (2,3), (3,4), (4,4);
 explain select * from t1 left join t2 on a=c where d in (4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
+1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where; Using join cache
 1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
 select * from t1 left join t2 on a=c where d in (4);
 a	b	c	d
@@ -2370,7 +2370,7 @@ a	b	c	d
 4	2	4	4
 explain select * from t1 left join t2 on a=c where d = 4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
+1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where; Using join cache
 1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
 select * from t1 left join t2 on a=c where d = 4;
 a	b	c	d
@@ -2716,7 +2716,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2
 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 
 t2.b like '%%' order by t2.b limit 0,1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	b,c	b	5	const	1	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ref	b,c	b	5	const	1	Using where; Using temporary; Using filesort; Using join cache
 1	SIMPLE	t3	index	PRIMARY,a,b	PRIMARY	8	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,t3;
@@ -2737,7 +2737,7 @@ INSERT INTO t2 VALUES (1,NULL), (2,10);
 ALTER TABLE t1 ENABLE KEYS;
 EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	b	b	5	NULL	2	Using index
+1	SIMPLE	t2	index	b	b	5	NULL	2	Using index; Using join cache
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
 a	b	a	b
@@ -2747,7 +2747,7 @@ a	b	a	b
 2	10	4	10
 EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	b	b	5	NULL	2	Using index
+1	SIMPLE	t2	index	b	b	5	NULL	2	Using index; Using join cache
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL;
 a	b	a	b
@@ -2901,11 +2901,11 @@ a
 4
 EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using join cache
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using join cache
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
 DROP TABLE t1,t2;
 select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;