Take into account that table scans may use indexes

Docs/manual.texi:
  Changelog

5 files changed, 22 insertions, 7 deletions

diff --git a/Docs/manual.texi b/Docs/manual.texi
index b48602164e6..90c51b00a13 100644
--- a/Docs/manual.texi
+++ b/Docs/manual.texi
@@ -48374,6 +48374,9 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}.
 
 @itemize @bullet
 @item
+Don't use table scan with BerkeleyDB and InnoDB tables when we can use
+an index that covers the whole row.
+@item
 Added sql-mode flag @code{NO_UNSIGNED_SUBTRACTION} to disable unsigned
 arithmetic rules when it comes to subtraction. (This will make MySQL 4.0
 behave more closely to 3.23 with @code{UNSIGNED} columns).
diff --git a/sql/ha_berkeley.h b/sql/ha_berkeley.h
index 587d70265fa..f30d4e12d6d 100644
--- a/sql/ha_berkeley.h
+++ b/sql/ha_berkeley.h
@@ -92,7 +92,7 @@ class ha_berkeley: public handler
 		    HA_NULL_KEY | HA_HAVE_KEY_READ_ONLY |
 		    HA_BLOB_KEY | HA_NOT_EXACT_COUNT |
 		    HA_PRIMARY_KEY_IN_READ_INDEX | HA_DROP_BEFORE_CREATE |
-		    HA_AUTO_PART_KEY),
+		    HA_AUTO_PART_KEY | HA_TABLE_SCAN_ON_INDEX),
     changed_rows(0),last_dup_key((uint) -1),version(0),using_ignore(0)
   {
   }
diff --git a/sql/ha_innodb.h b/sql/ha_innodb.h
index fb10975f30a..c1c9bd3ddb8 100644
--- a/sql/ha_innodb.h
+++ b/sql/ha_innodb.h
@@ -84,7 +84,8 @@ class ha_innobase: public handler
 			  HA_NO_WRITE_DELAYED |
 			  HA_PRIMARY_KEY_IN_READ_INDEX |
 			  HA_DROP_BEFORE_CREATE | HA_NOT_READ_PREFIX_LAST |
-			  HA_NO_PREFIX_CHAR_KEYS),
+			  HA_NO_PREFIX_CHAR_KEYS |
+			  HA_TABLE_SCAN_ON_INDEX),
 	  last_dup_key((uint) -1),
 	  start_of_scan(0)
   	{
diff --git a/sql/handler.h b/sql/handler.h
index 0add543f12c..8d8dfeb8da4 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -42,16 +42,13 @@
 #define HA_ADMIN_INVALID         -5
 
 /* Bits in bas_flag to show what database can do */
-
 #define HA_READ_NEXT		1	/* Read next record with same key */
 #define HA_READ_PREV		2	/* Read prev. record with same key */
 #define HA_READ_ORDER		4	/* Read through record-keys in order */
 #define HA_READ_RND_SAME	8	/* Read RND-record to KEY-record
 					   (To update with RND-read)	   */
 #define HA_KEYPOS_TO_RNDPOS	16	/* ha_info gives pos to record */
-#define HA_LASTKEY_ORDER	32	/* Next record gives next record
-					  according last record read (even
-					  if database is updated after read) */
+#define HA_TABLE_SCAN_ON_INDEX  32	/* No separate data/index file */
 #define HA_REC_NOT_IN_SEQ	64	/* ha_info don't return recnumber;
 					   It returns a position to ha_r_rnd */
 #define HA_ONLY_WHOLE_INDEX	128	/* Can't use part key searches */
@@ -78,6 +75,13 @@
 #define HA_CAN_FULLTEXT         (HA_NO_PREFIX_CHAR_KEYS*2)
 #define HA_CAN_SQL_HANDLER      (HA_CAN_FULLTEXT*2)
 
+/* Old not used flags */
+/*
+  Next record gives next record according last record read (even
+  if database is updated after read)
+*/
+#define HA_LASTKEY_ORDER	0
+
 	/* Parameters for open() (in register form->filestat) */
 	/* HA_GET_INFO does an implicit HA_ABORT_IF_LOCKED */
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 89082b40d87..4351ba3a738 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1939,10 +1939,17 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
       /*
 	Don't test table scan if it can't be better.
 	Prefer key lookup if we would use the same key for scanning.
+
+	Don't do a table scan on InnoDB tables, if we can read the used
+	parts of the row from any of the used index.
+	This is because table scans uses index and we would not win
+	anything by using a table scan.
       */
       if ((records >= s->found_records || best > s->read_time) &&
 	  !(s->quick && best_key && s->quick->index == best_key->key &&
-	    best_max_key_part >= s->table->quick_key_parts[best_key->key]))
+	    best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&
+	  !((s->table->file->option_flag() & HA_TABLE_SCAN_ON_INDEX) &&
+	    s->table->used_keys && best_key))
       {						// Check full join
 	if (s->on_expr)
 	{