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()).

1 files changed, 4 insertions, 0 deletions

diff --git a/sql/table.h b/sql/table.h
index 08b9a6e0a01..2bf390aee4d 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1104,6 +1104,8 @@ public:
       file->extra(HA_EXTRA_NO_KEYREAD);
     }
   }
+
+  bool update_const_key_parts(COND *conds);
 };
 
 
@@ -2088,6 +2090,8 @@ inline void mark_as_null_row(TABLE *table)
   bfill(table->null_flags,table->s->null_bytes,255);
 }
 
+bool is_simple_order(ORDER *order);
+
 #endif /* MYSQL_CLIENT */
 
 #endif /* TABLE_INCLUDED */