ORDER BY optimization

Fixed new bug when reading field types


client/mysql.cc:
  Fixed output for -T
libmysql/libmysql.c:
  Filled missing fields in new MYSQL_FIELD structure
  Fixed new bug when reading field types
mysql-test/r/order_by.result:
  Result after new order by optimization
sql/opt_range.cc:
  New ORDER BY optimization
sql/opt_range.h:
  New ORDER BY optimization
sql/set_var.cc:
  Speed optimization
sql/sql_select.cc:
  New ORDER BY optimization
  
  This alllows MySQL to change a ref/range index from (a,b) to (a,c) when 'b' was not used to find rows and one did
  ORDER BY a,c or ORDER BY c

7 files changed, 40 insertions, 20 deletions

diff --git a/client/mysql.cc b/client/mysql.cc
index f421756f0b4..812673d34c2 100644
--- a/client/mysql.cc
+++ b/client/mysql.cc
@@ -1793,9 +1793,8 @@ print_field_types(MYSQL_RES *result)
   MYSQL_FIELD	*field;  
   while ((field = mysql_fetch_field(result)))
   {
-    tee_fprintf(PAGER,"%s '%s' %d %d %d %d %d\n",
-		field->name,
-		field->table ? "" : field->table,
+    tee_fprintf(PAGER,"'%s.%s.%s.%s' %d %d %d %d %d\n",
+		field->catalog, field->db, field->table, field->name,
 		(int) field->type,
 		field->length, field->max_length, 
 		field->flags, field->decimals);
diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c
index 5971fda141b..514de2a87a6 100644
--- a/libmysql/libmysql.c
+++ b/libmysql/libmysql.c
@@ -1207,6 +1207,10 @@ unpack_fields(MYSQL_DATA *data,MEM_ROOT *alloc,uint fields,
       field->length= (uint) uint3korr(row->data[2]);
       field->type=   (enum enum_field_types) (uchar) row->data[3][0];
 
+      field->catalog=(char*)  "";
+      field->db=     (char*)  "";
+      field->catalog_length= 0;
+      field->db_length= 0;
       field->org_table_length=	field->table_length=	lengths[0];
       field->name_length=	lengths[1];
 
@@ -2883,7 +2887,7 @@ get_info:
 
   mysql->extra_info= net_field_length_ll(&pos); /* Maybe number of rec */
 
-  if (!(fields=read_rows(mysql,(MYSQL_FIELD*)0, protocol_41(mysql) ? 6 : 5)))
+  if (!(fields=read_rows(mysql,(MYSQL_FIELD*)0, protocol_41(mysql) ? 7 : 5)))
     DBUG_RETURN(1);
   if (!(mysql->fields=unpack_fields(fields,&mysql->field_alloc,
 				    (uint) field_count,0,
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 08176aeb2f8..3524b38e659 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -557,7 +557,7 @@ KEY StringField (FieldKey,StringVal(32))
 INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3');
 EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	FieldKey,LongField,StringField	StringField	36	const	3	Using where; Using filesort
+1	SIMPLE	t1	ref	FieldKey,LongField,StringField	LongField	36	const	3	Using where
 SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
 FieldKey	LongVal	StringVal
 1	0	2
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index d40f2e22ea2..6c1c49e23fc 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -379,8 +379,8 @@ SQL_SELECT::~SQL_SELECT()
 #undef index					// Fix for Unixware 7
 
 QUICK_SELECT::QUICK_SELECT(TABLE *table,uint key_nr,bool no_alloc)
-  :dont_free(0),error(0),index(key_nr),max_used_key_length(0),head(table),
-   it(ranges),range(0)
+  :dont_free(0),error(0),index(key_nr),max_used_key_length(0),
+   used_key_parts(0), head(table), it(ranges),range(0)
 {
   if (!no_alloc)
   {
@@ -2326,6 +2326,7 @@ get_quick_keys(PARAM *param,QUICK_SELECT *quick,KEY_PART *key,
 			 flag);
   set_if_bigger(quick->max_used_key_length,range->min_length);
   set_if_bigger(quick->max_used_key_length,range->max_length);
+  set_if_bigger(quick->used_key_parts, (uint) key_tree->part+1);
   if (!range)					// Not enough memory
     return 1;
   quick->ranges.push_back(range);
diff --git a/sql/opt_range.h b/sql/opt_range.h
index bc56fb99b4b..00736bfc22f 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -70,7 +70,7 @@ class QUICK_SELECT {
 public:
   bool next,dont_free;
   int error;
-  uint index,max_used_key_length;
+  uint index, max_used_key_length, used_key_parts;
   TABLE *head;
   handler *file;
   byte    *record;
diff --git a/sql/set_var.cc b/sql/set_var.cc
index fe6ee46068c..3145504951d 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -1697,7 +1697,7 @@ sys_var *find_sys_var(const char *str, uint length)
 int sql_set_variables(THD *thd, List<set_var_base> *var_list)
 {
   int error= 0;
-  List_iterator<set_var_base> it(*var_list);
+  List_iterator_fast<set_var_base> it(*var_list);
 
   set_var_base *var;
   while ((var=it++))
@@ -1813,5 +1813,5 @@ int set_var_password::update(THD *thd)
 
 #ifdef __GNUC__
 template class List<set_var_base>;
-template class List_iterator<set_var_base>;
+template class List_iterator_fast<set_var_base>;
 #endif
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 832058ff935..4167da5802a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6275,14 +6275,14 @@ is_subkey(KEY_PART_INFO *key_part, KEY_PART_INFO *ref_key_part,
 */
 
 static uint
-test_if_subkey(ORDER *order, TABLE *table, uint ref, key_map usable_keys)
+test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts,
+	       key_map usable_keys)
 {
   uint nr;
   uint min_length= (uint) ~0;
   uint best= MAX_KEY;
   uint not_used;
   KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part;
-  uint ref_key_parts= table->key_info[ref].key_parts;
   KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts;
   
   for (nr= 0; usable_keys; usable_keys>>= 1, nr++)
@@ -6317,10 +6317,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 			bool no_changes)
 {
   int ref_key;
+  uint ref_key_parts;
   TABLE *table=tab->table;
   SQL_SELECT *select=tab->select;
   key_map usable_keys;
   DBUG_ENTER("test_if_skip_sort_order");
+  LINT_INIT(ref_key_parts);
 
   /* Check which keys can be used to resolve ORDER BY */
   usable_keys= ~(key_map) 0;
@@ -6336,9 +6338,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 
   ref_key= -1;
   if (tab->ref.key >= 0)			// Constant range in WHERE
-    ref_key=tab->ref.key;
+  {
+    ref_key=	   tab->ref.key;
+    ref_key_parts= tab->ref.key_parts;
+  }
   else if (select && select->quick)		// Range found by opt_range
-    ref_key=select->quick->index;
+  {
+    ref_key=	   select->quick->index;
+    ref_key_parts= select->quick->used_key_parts;
+  }
 
   if (ref_key >= 0)
   {
@@ -6352,20 +6360,28 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
       /*
 	We come here when ref_key is not among usable_keys
       */
-      uint a;
-      if ((a= test_if_subkey(order, table, ref_key, usable_keys)) < MAX_KEY)
+      uint new_ref_key;
+      /*
+	If using index only read, only consider other possible index only
+	keys
+      */
+      if (table->used_keys & (((key_map) 1 << ref_key)))
+	usable_keys|= table->used_keys;
+      if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
+				       usable_keys)) < MAX_KEY)
       {
+	/* Found key that can be used to retrieve data in sorted order */
 	if (tab->ref.key >= 0)
 	{
-	  tab->ref.key= a;
-	  table->file->index_init(a);
+	  tab->ref.key= new_ref_key;
+	  table->file->index_init(new_ref_key);
 	}
 	else
 	{
-	  select->quick->index= a;
+	  select->quick->index= new_ref_key;
 	  select->quick->init();
 	}
-	ref_key= a;
+	ref_key= new_ref_key;
       }  
     }
     /* Check if we get the rows in requested sorted order by using the key */