BUG#12915: Added single-table UPDATE/DELTE ... ORDER BY ... LIMIT

optimization: now can use index to find records to update/delete
when there is no WHERE clause.


mysql-test/r/update.result:
  Testcase for BUG#12915
mysql-test/t/update.test:
  Testcase for BUG#12915
sql/mysql_priv.h:
  BUG#12915: Added init_read_record_idx function.
sql/opt_range.cc:
  BUG#12915: Added get_index_for_order() - find an index that can be
  used to get first N table records in given ordering cheaper then 
  one would with full table scan.
sql/opt_range.h:
  BUG#12915: Added get_index_for_order() function
sql/records.cc:
  BUG#12915: Added init_read_record_idx(), rr_index() that allow to scan
  index using init_read_record()/read_record.read_record()
sql/sql_delete.cc:
  BUG#12915: Added single-table DELETE ... ORDER BY ... LIMIT optimization: 
  now can use index to find records to delete when there is no WHERE clause.
sql/sql_update.cc:
  BUG#12915: Added single-table UPDATE ... ORDER BY ... LIMIT optimization: 
  now can use index to find records to update when there is no WHERE clause.
sql/structs.h:
  BUG#12915: Added init_read_record_idx(), rr_index() that allow to scan
  index using init_read_record()/READ_RECORD::read_record()

1 files changed, 32 insertions, 14 deletions

diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 1dd52a2ba74..079a301818c 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -37,6 +37,7 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   bool 		using_limit=limit != HA_POS_ERROR;
   bool		transactional_table, log_delayed, safe_update, const_cond; 
   ha_rows	deleted;
+  uint usable_index= MAX_KEY;
   DBUG_ENTER("mysql_delete");
 
   if ((open_and_lock_tables(thd, table_list)))
@@ -119,30 +120,47 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
     tables.table = table;
     tables.alias = table_list->alias;
 
-    table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
-                                             MYF(MY_FAE | MY_ZEROFILL));
       if (thd->lex->select_lex.setup_ref_array(thd, order->elements) ||
 	  setup_order(thd, thd->lex->select_lex.ref_pointer_array, &tables,
-		      fields, all_fields, (ORDER*) order->first) ||
-	  !(sortorder=make_unireg_sortorder((ORDER*) order->first, &length)) ||
+                    fields, all_fields, (ORDER*) order->first))
+    {
+      delete select;
+      free_underlaid_joins(thd, &thd->lex->select_lex);
+      DBUG_RETURN(-1);			// This will force out message
+    }
+    
+    if (!select && limit != HA_POS_ERROR)
+      usable_index= get_index_for_order(table, (ORDER*)(order->first), limit);
+
+    if (usable_index == MAX_KEY)
+    {
+      table->sort.io_cache= (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
+                                                   MYF(MY_FAE | MY_ZEROFILL));
+    
+      if ( !(sortorder=make_unireg_sortorder((ORDER*) order->first, &length)) ||
 	  (table->sort.found_records = filesort(thd, table, sortorder, length,
 					   select, HA_POS_ERROR,
 					   &examined_rows))
 	  == HA_POS_ERROR)
-    {
+      {
+        delete select;
+        free_underlaid_joins(thd, &thd->lex->select_lex);
+        DBUG_RETURN(-1);			// This will force out message
+      }
+      /*
+        Filesort has already found and selected the rows we want to delete,
+        so we don't need the where clause
+      */
       delete select;
-      free_underlaid_joins(thd, &thd->lex->select_lex);
-      DBUG_RETURN(-1);			// This will force out message
+      select= 0;
     }
-    /*
-      Filesort has already found and selected the rows we want to delete,
-      so we don't need the where clause
-    */
-    delete select;
-    select= 0;
   }
 
-  init_read_record(&info,thd,table,select,1,1);
+  if (usable_index==MAX_KEY)
+    init_read_record(&info,thd,table,select,1,1);
+  else
+    init_read_record_idx(&info, thd, table, 1, usable_index);
+
   deleted=0L;
   init_ftfuncs(thd, &thd->lex->select_lex, 1);
   thd->proc_info="updating";