Bug #30584: delete with order by and limit clauses does not

            use limit efficiently
Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a
            filesort even if not required

Also two bugs reported after QA review (before the commit
of bugs above to public trees, no documentation needed):

Bug #53737: Performance regressions after applying patch
            for bug 36569
Bug #53742: UPDATEs have no effect after applying patch
            for bug 36569


Execution of single-table UPDATE and DELETE statements did not use the 
same optimizer as was used in the compilation of SELECT statements. 
Instead, it had an optimizer of its own that did not take into account 
that you can omit sorting by retrieving rows using an index.

Extra optimization has been added: when applicable, single-table 
UPDATE/DELETE statements use an existing index instead of filesort. A 
corresponding SELECT query would do the former.

Also handling of the DESC ordering expression has been added when
reverse index scan is applicable.

From now on most single table UPDATE and DELETE statements show the 
same disk access patterns as the corresponding SELECT query. We verify 
this by comparing the result of SHOW STATUS LIKE 'Sort%

Currently the get_index_for_order function 
a) checks quick select index (if any) for compatibility with the
   ORDER expression list or
b) chooses the cheapest available compatible index, but only if 
   the index scan is cheaper than filesort.
Second way is implemented by the new test_if_cheaper_ordering
function (extracted part the test_if_skip_sort_order()).



mysql-test/r/log_state.result:
  Updated result for optimized query, bug #36569.
mysql-test/r/single_delete_update.result:
  Test case for bug #30584, bug #36569 and bug #53742.
mysql-test/r/update.result:
  Updated result for optimized query, bug #30584.
  Note:
  "Handler_read_last 1" omitted, see bug 52312:
  lost Handler_read_last status variable.
mysql-test/t/single_delete_update.test:
  Test case for bug #30584, bug #36569 and bug #53742.
sql/opt_range.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  * get_index_for_order() has been rewritten entirely and moved
    to sql_select.cc
  
  New QUICK_RANGE_SELECT::make_reverse method has been added.
sql/opt_range.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  * get_index_for_order() has been rewritten entirely and moved
    to sql_select.cc
  
  New functions:
  * QUICK_SELECT_I::make_reverse()
  * SQL_SELECT::set_quick()
sql/records.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  * init_read_record_idx() has been modified to allow reverse index scan
  
  New functions:
  * rr_index_last()
  * rr_index_desc()
sql/records.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  init_read_record_idx() has been modified to allow reverse index scan
sql/sql_delete.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
      
  mysql_delete: an optimization has been added to skip
  unnecessary sorting with ORDER BY clause where select
  result ordering is acceptable.
sql/sql_select.cc:
  Bug #30584, bug #36569, bug #53737, bug #53742:
    UPDATE/DELETE ... WHERE ... ORDER BY...  always does a filesort
    even if not required
      
  The const_expression_in_where function has been modified
  to accept both Item and Field pointers.
  
  New functions:
  * get_index_for_order()
  * test_if_cheaper_ordering() has been extracted from
    test_if_skip_sort_order() to share with get_index_for_order()
  * simple_remove_const()
sql/sql_select.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
      
  New functions:
  * test_if_cheaper_ordering()
  * simple_remove_const()
  * get_index_for_order()
sql/sql_update.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
      
  mysql_update: an optimization has been added to skip
  unnecessary sorting with ORDER BY clause where a select
  result ordering is acceptable.
sql/table.cc:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  New functions:
  * TABLE::update_const_key_parts()
  * is_simple_order()
sql/table.h:
  Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY...
                          always does a filesort even if not required
  
  New functions:
  * TABLE::update_const_key_parts()
  * is_simple_order()

1 files changed, 23 insertions, 92 deletions

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 9363b637862..995582fc6ee 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1842,96 +1842,6 @@ SEL_ARG *SEL_ARG::clone_tree(RANGE_OPT_PARAM *param)
 
 
 /*
-  Find the best index to retrieve first N records in given order
-
-  SYNOPSIS
-    get_index_for_order()
-      table  Table to be accessed
-      order  Required ordering
-      limit  Number of records that will be retrieved
-
-  DESCRIPTION
-    Find the best index that allows to retrieve first #limit records in the 
-    given order cheaper then one would retrieve them using full table scan.
-
-  IMPLEMENTATION
-    Run through all table indexes and find the shortest index that allows
-    records to be retrieved in given order. We look for the shortest index
-    as we will have fewer index pages to read with it.
-
-    This function is used only by UPDATE/DELETE, so we take into account how
-    the UPDATE/DELETE code will work:
-     * index can only be scanned in forward direction
-     * HA_EXTRA_KEYREAD will not be used
-    Perhaps these assumptions could be relaxed.
-
-  RETURN
-    Number of the index that produces the required ordering in the cheapest way
-    MAX_KEY if no such index was found.
-*/
-
-uint get_index_for_order(TABLE *table, ORDER *order, ha_rows limit)
-{
-  uint idx;
-  uint match_key= MAX_KEY, match_key_len= MAX_KEY_LENGTH + 1;
-  ORDER *ord;
-  
-  for (ord= order; ord; ord= ord->next)
-    if (!ord->asc)
-      return MAX_KEY;
-
-  for (idx= 0; idx < table->s->keys; idx++)
-  {
-    if (!(table->keys_in_use_for_query.is_set(idx)))
-      continue;
-    KEY_PART_INFO *keyinfo= table->key_info[idx].key_part;
-    uint n_parts=  table->key_info[idx].key_parts;
-    uint partno= 0;
-    
-    /* 
-      The below check is sufficient considering we now have either BTREE 
-      indexes (records are returned in order for any index prefix) or HASH 
-      indexes (records are not returned in order for any index prefix).
-    */
-    if (!(table->file->index_flags(idx, 0, 1) & HA_READ_ORDER))
-      continue;
-    for (ord= order; ord && partno < n_parts; ord= ord->next, partno++)
-    {
-      Item *item= order->item[0];
-      if (!(item->type() == Item::FIELD_ITEM &&
-           ((Item_field*)item)->field->eq(keyinfo[partno].field)))
-        break;
-    }
-    
-    if (!ord && table->key_info[idx].key_length < match_key_len)
-    {
-      /* 
-        Ok, the ordering is compatible and this key is shorter then
-        previous match (we want shorter keys as we'll have to read fewer
-        index pages for the same number of records)
-      */
-      match_key= idx;
-      match_key_len= table->key_info[idx].key_length;
-    }
-  }
-
-  if (match_key != MAX_KEY)
-  {
-    /* 
-      Found an index that allows records to be retrieved in the requested 
-      order. Now we'll check if using the index is cheaper then doing a table
-      scan.
-    */
-    double full_scan_time= table->file->scan_time();
-    double index_scan_time= table->file->read_time(match_key, 1, limit);
-    if (index_scan_time > full_scan_time)
-      match_key= MAX_KEY;
-  }
-  return match_key;
-}
-
-
-/*
   Table rows retrieval plan. Range optimizer creates QUICK_SELECT_I-derived
   objects from table read plans.
 */
@@ -8977,7 +8887,6 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUICK_RANGE_SELECT *q,
   }
   rev_it.rewind();
   q->dont_free=1;				// Don't free shared mem
-  delete q;
 }
 
 
@@ -9067,6 +8976,27 @@ int QUICK_SELECT_DESC::get_next()
 }
 
 
+/**
+  Create a compatible quick select with the result ordered in an opposite way
+
+  @param used_key_parts_arg  Number of used key parts
+
+  @retval NULL in case of errors (OOM etc)
+  @retval pointer to a newly created QUICK_SELECT_DESC if success
+*/
+
+QUICK_SELECT_I *QUICK_RANGE_SELECT::make_reverse(uint used_key_parts_arg)
+{
+  QUICK_SELECT_DESC *new_quick= new QUICK_SELECT_DESC(this, used_key_parts_arg);
+  if (new_quick == NULL || new_quick->error != 0)
+  {
+    delete new_quick;
+    return NULL;
+  }
+  return new_quick;
+}
+
+
 /*
   Compare if found key is over max-value
   Returns 0 if key <= range->max_key
@@ -11673,6 +11603,7 @@ void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose)
   /* purecov: end */    
 }
 
+
 void QUICK_INDEX_MERGE_SELECT::dbug_dump(int indent, bool verbose)
 {
   List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);
@@ -11761,7 +11692,7 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
 }
 
 
-#endif /* NOT_USED */
+#endif /* !DBUG_OFF */
 
 /*****************************************************************************
 ** Instantiate templates