Make EXPLAIN better at displaying MRR/BKA:

- "Using MRR" is no longer shown with range access.
- Instead, both range and BKA accesses will show one of the following:
  = "Rowid-ordered scan"
  = "Key-ordered scan"
  = "Key-ordered Rowid-ordered scan"
depending on whether DS-MRR implementation will do scan keys in order, rowids in order,
or both.
- The patch also introduces a way for other storage engines/MRR implementations to
  pass information to EXPLAIN output about the properties of employed MRR scans.

6 files changed, 145 insertions, 7 deletions

diff --git a/sql/handler.h b/sql/handler.h
index aabb60e4252..36cb81017c6 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1358,6 +1358,15 @@ void get_sweep_read_cost(TABLE *table, ha_rows nrows, bool interrupted,
 */
 #define HA_MRR_MATERIALIZED_KEYS 256
 
+#define HA_MRR_IMPLEMENTATION_FLAG1 512
+#define HA_MRR_IMPLEMENTATION_FLAG2 1024
+#define HA_MRR_IMPLEMENTATION_FLAG3 2048
+#define HA_MRR_IMPLEMENTATION_FLAG4 4096
+#define HA_MRR_IMPLEMENTATION_FLAG5 8192
+#define HA_MRR_IMPLEMENTATION_FLAG6 16384
+
+#define HA_MRR_IMPLEMENTATION_FLAGS \
+  (512 | 1024 | 2048 | 4096 | 8192 | 16384)
 
 /*
   This is a buffer area that the handler can use to store rows.
@@ -1863,14 +1872,40 @@ public:
   virtual ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                               void *seq_init_param, 
                                               uint n_ranges, uint *bufsz,
-                                              uint *flags, COST_VECT *cost);
+                                              uint *mrr_mode, COST_VECT *cost);
   virtual ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
                                         uint key_parts, uint *bufsz, 
-                                        uint *flags, COST_VECT *cost);
+                                        uint *mrr_mode, COST_VECT *cost);
   virtual int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param,
-                                    uint n_ranges, uint mode, 
+                                    uint n_ranges, uint mrr_mode, 
                                     HANDLER_BUFFER *buf);
   virtual int multi_range_read_next(range_id_t *range_info);
+  /*
+    Return string representation of the MRR plan.
+
+    This is intended to be used for EXPLAIN, via the following scenario:
+    1. SQL layer calls handler->multi_range_read_info().
+    1.1. Storage engine figures out whether it will use some non-default
+         MRR strategy, sets appropritate bits in *mrr_mode, and returns 
+         control to SQL layer
+    2. SQL layer remembers the returned mrr_mode
+    3. SQL layer compares various options and choses the final query plan. As
+       a part of that, it makes a choice of whether to use the MRR strategy
+       picked in 1.1
+    4. EXPLAIN code converts the query plan to its text representation. If MRR
+       strategy is part of the plan, it calls
+       multi_range_read_explain_info(mrr_mode) to get a text representation of
+       the picked MRR strategy.
+
+    @param mrr_mode   Mode which was returned by multi_range_read_info[_const]
+    @param str        INOUT string to be printed for EXPLAIN
+    @param str_end    End of the string buffer. The function is free to put the 
+                      string into [str..str_end] memory range.
+  */
+  virtual int multi_range_read_explain_info(uint mrr_mode, char *str, 
+                                            size_t size)
+  { return 0; }
+
   virtual int read_range_first(const key_range *start_key,
                                const key_range *end_key,
                                bool eq_range, bool sorted);
diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
index 2bea804beef..37ce7c3f840 100644
--- a/sql/multi_range_read.cc
+++ b/sql/multi_range_read.cc
@@ -1372,7 +1372,7 @@ ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows,
   DBUG_ASSERT(!res);
 
   if ((*flags & HA_MRR_USE_DEFAULT_IMPL) || 
-      choose_mrr_impl(keyno, rows, &def_flags, &def_bufsz, cost))
+      choose_mrr_impl(keyno, rows, flags, bufsz, cost))
   {
     /* Default implementation is choosen */
     DBUG_PRINT("info", ("Default MRR implementation choosen"));
@@ -1517,11 +1517,13 @@ bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags,
   bool doing_cpk_scan= check_cpk_scan(thd, keyno, *flags); 
   bool using_cpk= test(keyno == table->s->primary_key &&
                        primary_file->primary_key_is_clustered());
+  *flags &= ~HA_MRR_IMPLEMENTATION_FLAGS;
   if (thd->variables.optimizer_use_mrr == 2 || *flags & HA_MRR_INDEX_ONLY ||
       (using_cpk && !doing_cpk_scan) || key_uses_partial_cols(table, keyno))
   {
     /* Use the default implementation */
     *flags |= HA_MRR_USE_DEFAULT_IMPL;
+    *flags &= ~HA_MRR_IMPLEMENTATION_FLAGS;
     return TRUE;
   }
 
@@ -1549,9 +1551,24 @@ bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags,
     *cost= dsmrr_cost;
     res= FALSE;
 
+
+    if ((using_cpk && doing_cpk_scan) ||
+        (optimizer_flag(thd, OPTIMIZER_SWITCH_MRR_SORT_KEYS) &&
+         *flags & HA_MRR_SINGLE_POINT))
+    {
+      *flags |= DSMRR_IMPL_SORT_KEYS;
+    }
+    
+    if (!(using_cpk && doing_cpk_scan) &&
+        !(*flags & HA_MRR_INDEX_ONLY))
+    {
+      *flags |= DSMRR_IMPL_SORT_ROWIDS;
+    }
+    /*
     if ((*flags & HA_MRR_SINGLE_POINT) && 
          optimizer_flag(thd, OPTIMIZER_SWITCH_MRR_SORT_KEYS))
       *flags |= HA_MRR_MATERIALIZED_KEYS;
+    */
   }
   else
   {
@@ -1561,6 +1578,38 @@ bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags,
   return res;
 }
 
+/*
+  Take the flags we've returned previously and print one of
+  - Key-ordered scan
+  - Rowid-ordered scan
+  - Key-ordered Rowid-ordered scan
+*/
+
+int DsMrr_impl::dsmrr_explain_info(uint mrr_mode, char *str, size_t size)
+{
+  const char *key_ordered=   "Key-ordered scan";
+  const char *rowid_ordered= "Rowid-ordered scan";
+  const char *both_ordered=  "Key-ordered Rowid-ordered scan";
+  const char *used_str="";
+  const uint BOTH_FLAGS= (DSMRR_IMPL_SORT_KEYS | DSMRR_IMPL_SORT_ROWIDS);
+
+  if (!(mrr_mode & HA_MRR_USE_DEFAULT_IMPL))
+  {
+    if ((mrr_mode & BOTH_FLAGS) == BOTH_FLAGS)
+      used_str= both_ordered;
+    else if (mrr_mode & DSMRR_IMPL_SORT_KEYS)
+      used_str= key_ordered;
+    else if (mrr_mode & DSMRR_IMPL_SORT_ROWIDS)
+      used_str= rowid_ordered;
+
+    uint used_str_len= strlen(used_str);
+    uint copy_len= min(used_str_len, size);
+    memcpy(str, used_str, size);
+    return copy_len;
+  }
+  return 0;
+}
+
 
 static void get_sort_and_sweep_cost(TABLE *table, ha_rows nrows, COST_VECT *cost);
 
diff --git a/sql/multi_range_read.h b/sql/multi_range_read.h
index 08ac1b6f6a4..1b72e71944d 100644
--- a/sql/multi_range_read.h
+++ b/sql/multi_range_read.h
@@ -395,6 +395,9 @@ public:
 };
 
 
+#define DSMRR_IMPL_SORT_KEYS   HA_MRR_IMPLEMENTATION_FLAG1
+#define DSMRR_IMPL_SORT_ROWIDS HA_MRR_IMPLEMENTATION_FLAG2
+
 /*
   DS-MRR implementation for one table. Create/use one object of this class for
   each ha_{myisam/innobase/etc} object. That object will be further referred to
@@ -548,6 +551,8 @@ public:
   ha_rows dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq, 
                             void *seq_init_param, uint n_ranges, uint *bufsz,
                             uint *flags, COST_VECT *cost);
+
+  int dsmrr_explain_info(uint mrr_mode, char *str, size_t size);
 private:
   /* Buffer to store (key, range_id) pairs */
   Lifo_buffer *key_buffer;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index e952cf3e2ef..44b953a27fb 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2491,8 +2491,36 @@ void JOIN_CACHE::print_explain_comment(String *str)
   str->append(join_alg);
   str->append(STRING_WITH_LEN(" join"));
   str->append(STRING_WITH_LEN(")"));
- }
-   
+}
+
+
+static void add_mrr_explain_info(String *str, uint mrr_mode, handler *file)
+{
+  char mrr_str_buf[128]={0};
+  int len;
+  len= file->multi_range_read_explain_info(mrr_mode, mrr_str_buf,
+                                           sizeof(mrr_str_buf));
+  if (len > 0)
+  {
+    str->append(STRING_WITH_LEN("; "));
+    str->append(mrr_str_buf, len);
+  }
+}
+
+
+void JOIN_CACHE_BKA::print_explain_comment(String *str)
+{
+  JOIN_CACHE::print_explain_comment(str); 
+  add_mrr_explain_info(str, mrr_mode, join_tab->table->file);
+}
+
+
+void JOIN_CACHE_BKAH::print_explain_comment(String *str)
+{
+  JOIN_CACHE::print_explain_comment(str); 
+  add_mrr_explain_info(str, mrr_mode, join_tab->table->file);
+}
+
 
 /* 
   Initialize a hashed join cache       
diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h
index 5498192122f..920b7a507c1 100644
--- a/sql/sql_join_cache.h
+++ b/sql/sql_join_cache.h
@@ -637,7 +637,7 @@ public:
   enum_nested_loop_state join_records(bool skip_last);
 
   /* Add a comment on the join algorithm employed by the join cache */
-  void print_explain_comment(String *str);
+  virtual void print_explain_comment(String *str);
 
   virtual ~JOIN_CACHE() {}
   void reset_join(JOIN *j) { join= j; }
@@ -1315,6 +1315,7 @@ public:
   /* Check index condition of the joined table for a record from BKA cache */
   bool skip_index_tuple(range_id_t range_info);
 
+  void print_explain_comment(String *str);
 };
 
 
@@ -1404,4 +1405,6 @@ public:
 
   /* Check index condition of the joined table for a record from BKAH cache */
   bool skip_index_tuple(range_id_t range_info);
+
+  void print_explain_comment(String *str);
 };
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6d5dc963320..37547024318 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -19638,12 +19638,30 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
 	if (table->reginfo.not_exists_optimize)
 	  extra.append(STRING_WITH_LEN("; Not exists"));
 
+        /*
         if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
             !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
              HA_MRR_USE_DEFAULT_IMPL))
         {
 	  extra.append(STRING_WITH_LEN("; Using MRR"));
         }
+        */
+        if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
+        {
+          char mrr_str_buf[128];
+          mrr_str_buf[0]=0;
+          int len;
+          uint mrr_flags= 
+            ((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags;
+          len= table->file->multi_range_read_explain_info(mrr_flags,
+                                                          mrr_str_buf,
+                                                          sizeof(mrr_str_buf));
+          if (len > 0)
+          {
+            extra.append(STRING_WITH_LEN("; "));
+            extra.append(mrr_str_buf, len);
+          }
+        }
 
 	if (need_tmp_table)
 	{