Fixed bug in the optimiser for FULL TABLE SCAN case:

to estimate correctly cost of full table scan we should take into
account rows read and skipped on each iteration.


mysql-test/r/distinct.result:
  Fixed test suite: now full scan is used
  less often
mysql-test/r/join_outer.result:
  Fixed test suite: now full scan is used
  less often
mysql-test/r/select_safe.result:
  Fixed test suite: now full scan is used
  less often

4 files changed, 27 insertions, 29 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 1dbccb65748..da0c6a5eb72 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3);
 INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
 explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t3	index	a	a	5	NULL	6	Using index; Using temporary
-t2	index	a	a	4	NULL	5	Using index; Distinct
-t1	eq_ref	PRIMARY	PRIMARY	4	t2.a	1	Using where; Distinct
+t2	index	a	a	4	NULL	5	Using index; Using temporary
+t1	eq_ref	PRIMARY	PRIMARY	4	t2.a	1	
+t3	index	a	a	5	NULL	5	Using where; Using index
 SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 a
 1
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 8f3f82201c3..debec01fbdc 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -615,7 +615,7 @@ INSERT INTO t2 VALUES (1,1);
 explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	NULL	NULL	NULL	NULL	2	
-t2	index	id	id	8	NULL	1	Using where; Using index; Not exists
+t2	ref	id	id	4	t1.id	1	Using where; Using index; Not exists
 SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
 id	name	id	idx
 2	no	NULL	NULL
diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result
index c4e5984d360..1ee1368d029 100644
--- a/mysql-test/r/select_safe.result
+++ b/mysql-test/r/select_safe.result
@@ -70,7 +70,7 @@ insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(nu
 explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	ALL	b	NULL	NULL	NULL	21	
-t2	ALL	b	NULL	NULL	NULL	16	Using where
+t2	ref	b	b	21	t1.b	6	Using where
 set MAX_SEEKS_FOR_KEY=1;
 explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
 table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6f6d21f1f8b..3738382928f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2133,33 +2133,31 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	    s->table->used_keys && best_key) &&
 	  !(s->table->force_index && best_key))
       {						// Check full join
-	ha_rows rnd_records= s->found_records;
-	if (s->on_expr)
-	{
-	  tmp=rows2double(rnd_records);		// Can't use read cache
-	}
-	else
-	{
-	  tmp=(double) s->read_time;
-	  /* Calculate time to read previous rows through cache */
-	  tmp*=(1.0+floor((double) cache_record_length(join,idx)*
-			  record_count /
-			  (double) thd->variables.join_buff_size));
-	}
-
-	/*
-	  If there is a restriction on the table, assume that 25% of the
-	  rows can be skipped on next part.
-	  This is to force tables that this table depends on before this
-	  table
-	*/
-	if (found_constrain)
-	  rnd_records-= rnd_records/4;
-
+        /*
+          Estimate cost of reading table. Note, that we don't read a table
+          on each iteration as in most cases join buffer is in use. 
+        */
+        tmp= (double) s->read_time;
+        /*
+          In case of full scan we check every row in the table: 
+          here we take into account rows read and skipped, as well as rows
+          passed to next select
+         */
+        
 	if (best == DBL_MAX ||
-	    (tmp  + record_count/(double) TIME_FOR_COMPARE*rnd_records <
+	    (tmp  + record_count/(double) TIME_FOR_COMPARE*s->records <
 	     best + record_count/(double) TIME_FOR_COMPARE*records))
 	{
+          /*
+            If there is a restriction on the table, assume that 25% of the
+            rows can be skipped on next part.
+            This is to force tables that this table depends on before this
+            table
+          */
+          ha_rows rnd_records= s->found_records;
+          if (found_constrain)
+            rnd_records-= rnd_records/4;
+          
 	  /*
 	    If the table has a range (s->quick is set) make_join_select()
 	    will ensure that this will be used