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, 11 insertions, 1 deletions

diff --git a/sql/opt_range.h b/sql/opt_range.h
index 72f2eb4b51d..7f05bdb64f0 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -352,6 +352,11 @@ public:
   */
   virtual void dbug_dump(int indent, bool verbose)= 0;
 #endif
+
+  /*
+    Returns a QUICK_SELECT with reverse order of to the index.
+  */
+  virtual QUICK_SELECT_I *make_reverse(uint used_key_parts_arg) { return NULL; }
 };
 
 
@@ -437,6 +442,7 @@ public:
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif
+  QUICK_SELECT_I *make_reverse(uint used_key_parts_arg);
 private:
   /* Default copy ctor used by QUICK_SELECT_DESC */
 };
@@ -782,6 +788,10 @@ public:
   int get_next();
   bool reverse_sorted() { return 1; }
   int get_type() { return QS_TYPE_RANGE_DESC; }
+  QUICK_SELECT_I *make_reverse(uint used_key_parts_arg)
+  {
+    return this; // is already reverse sorted
+  }
 private:
   bool range_reads_after_key(QUICK_RANGE *range);
   int reset(void) { rev_it.rewind(); return QUICK_RANGE_SELECT::reset(); }
@@ -807,6 +817,7 @@ class SQL_SELECT :public Sql_alloc {
   SQL_SELECT();
   ~SQL_SELECT();
   void cleanup();
+  void set_quick(QUICK_SELECT_I *new_quick) { delete quick; quick= new_quick; }
   bool check_quick(THD *thd, bool force_quick_range, ha_rows limit)
   {
     key_map tmp;
@@ -833,7 +844,6 @@ public:
 QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
                                              struct st_table_ref *ref,
                                              ha_rows records);
-uint get_index_for_order(TABLE *table, ORDER *order, ha_rows limit);
 SQL_SELECT *make_select(TABLE *head, table_map const_tables,
 			table_map read_tables, COND *conds,
                         bool allow_null_cond,  int *error);