Back-ported the patch of the mysql-5.6 code line that

fixed several defects in the greedy optimization:

1) The greedy optimizer calculated the 'compare-cost' (CPU-cost)
   for iterating over the partial plan result at each level in
   the query plan as 'record_count / (double) TIME_FOR_COMPARE'

   This cost was only used locally for 'best' calculation at each
   level, and *not* accumulated into the total cost for the query plan.

   This fix added the 'CPU-cost' of processing 'current_record_count'
   records at each level to 'current_read_time' *before* it is used as
   'accumulated cost' argument to recursive 
   best_extension_by_limited_search() calls. This ensured that the
   cost of a huge join-fanout early in the QEP was correctly
   reflected in the cost of the final QEP.

   To get identical cost for a 'best' optimized query and a
   straight_join with the same join order, the same change was also
   applied to optimize_straight_join() and get_partial_join_cost()

2) Furthermore to get equal cost for 'best' optimized query and a
   straight_join the new code substrcated the same '0.001' in
   optimize_straight_join() as it had been already done in
   best_extension_by_limited_search()

3) When best_extension_by_limited_search() aggregated the 'best' plan a
   plan was 'best' by the check :

   'if ((search_depth == 1) || (current_read_time < join->best_read))'

   The term '(search_depth == 1' incorrectly caused a new best plan to be
   collected whenever the specified 'search_depth' was reached - even if
   this partial query plan was more expensive than what we had already
   found.

1 files changed, 3 insertions, 1 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 3aa5a188a26..d06480391be 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2738,10 +2738,12 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
     }
     
     table_map dups_removed_fanout= 0;
+    double current_fanout= prefix_rec_count;
     for (uint j= first_dupsweedout_table; j <= idx; j++)
     {
       POSITION *p= join->positions + j;
-      dups_cost += p->read_time;
+      current_fanout *= p->records_read;
+      dups_cost += p->read_time + current_fanout / TIME_FOR_COMPARE;
       if (p->table->emb_sj_nest)
       {
         sj_inner_fanout *= p->records_read;