Better optimization for InnoDB and BDB tables for ORDER BY

Build-tools/Do-compile:
  Merge with 4.0
Docs/manual.texi:
  Changelog
mysql-test/r/innodb.result:
  New tests cases for ORDER BY
mysql-test/r/myisam.result:
  New tests cases for ORDER BY
mysql-test/t/innodb.test:
  New tests cases for ORDER BY
mysql-test/t/myisam.test:
  New tests cases for ORDER BY

4 files changed, 9 insertions, 1 deletions

diff --git a/sql/ha_berkeley.h b/sql/ha_berkeley.h
index 561e06229fa..ab1ead5a3e9 100644
--- a/sql/ha_berkeley.h
+++ b/sql/ha_berkeley.h
@@ -107,6 +107,7 @@ class ha_berkeley: public handler
   uint extra_rec_buf_length()	 { return BDB_HIDDEN_PRIMARY_KEY_LENGTH; }
   ha_rows estimate_number_of_rows();
   bool fast_key_read()	   { return 1;}
+  key_map keys_to_use_for_scanning() { return ~(key_map) 0; }
   bool has_transactions()  { return 1;}
 
   int open(const char *name, int mode, uint test_if_locked);
diff --git a/sql/ha_innobase.h b/sql/ha_innobase.h
index 95bba76c842..83e43b1d662 100644
--- a/sql/ha_innobase.h
+++ b/sql/ha_innobase.h
@@ -101,6 +101,7 @@ class ha_innobase: public handler
 				bytes */
   	uint max_key_length()    const { return 7000; }
   	bool fast_key_read()	 { return 1;}
+	key_map keys_to_use_for_scanning() { return ~(key_map) 0; }
   	bool has_transactions()  { return 1;}
 
   	int open(const char *name, int mode, uint test_if_locked);
diff --git a/sql/handler.h b/sql/handler.h
index f6e9ad61d94..560420a480d 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -223,6 +223,7 @@ public:
     { return ulonglong2double(data_file_length) / IO_SIZE + 1; }
   virtual double read_time(ha_rows rows) { return rows; }
   virtual bool fast_key_read() { return 0;}
+  virtual key_map keys_to_use_for_scanning() { return 0; }
   virtual bool has_transactions(){ return 0;}
   virtual uint extra_rec_buf_length() { return 0; }
   virtual ha_rows estimate_number_of_rows() { return records+EXTRA_RECORDS; }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 774be3679a2..9456211a7cc 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5199,7 +5199,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit)
       retrieving all rows through an index.
     */
     if (select_limit >= table->file->records)
-      keys&= table->used_keys;
+      keys&= (table->used_keys | table->file->keys_to_use_for_scanning());
 
     for (nr=0; keys ; keys>>=1, nr++)
     {
@@ -5213,6 +5213,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit)
 				    join_init_read_last_with_key);
 	  table->file->index_init(nr);
 	  tab->type=JT_NEXT;	// Read with index_first(), index_next()
+	  if (table->used_keys & ((key_map) 1 << nr))
+	  {
+	    table->key_read=1;
+	    table->file->extra(HA_EXTRA_KEYREAD);
+	  }
 	  DBUG_RETURN(1);
 	}
       }