[Commits] Rev 3465: MDEV-406: EXPLAIN ANALYZE in file:///home/psergey/dev2/5.5-show-explain-r30/

Sergey Petrunya psergey at askmonty.org
Mon Sep 3 18:11:32 EEST 2012


At file:///home/psergey/dev2/5.5-show-explain-r30/

------------------------------------------------------------
revno: 3465
revision-id: psergey at askmonty.org-20120903151124-s1y9vrz3k4ezy642
parent: psergey at askmonty.org-20120809163809-izpuld11dt8z85uw
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.5-show-explain-r30
timestamp: Mon 2012-09-03 19:11:24 +0400
message:
  MDEV-406: EXPLAIN ANALYZE 
  - First code, it is possible to get the data for joins/unions
  - Numbers for join buffering are likely to be incorrect
  - Same for subqueries (what should 'loops' show?)
=== added file 'mysql-test/r/explain_analyze.result'
--- a/mysql-test/r/explain_analyze.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/explain_analyze.result	2012-09-03 15:11:24 +0000
@@ -0,0 +1,29 @@
+drop table if exists t0, t1, t2, t3, t4;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+explain analyze select * from t0 where a<3;
+id	select_type	table	type	possible_keys	key	key_len	ref	loops	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	1	10	10	100.00	30.00	Using where
+explain
+select * from t0 A where a<3 union select * from t0 A where a > 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	UNION	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain analyze 
+select * from t0 A where a<3 union select * from t0 A where a > 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	loops	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	1	10	10	100.00	30.00	Using where
+2	UNION	A	ALL	NULL	NULL	NULL	NULL	1	10	10	100.00	40.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	
+create table t1 (a int, b int);
+insert into t1 select a, a as b from t0;
+set @tmp_jcl= @@join_cache_level;
+set join_cache_level=0;
+explain analyze select * from t0, t1 where t0.a=t1.a and t1.b< 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	loops	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	1	10	10	100.00	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10	10	100.00	3.00	Using where
+set join_cache_level= @tmp_jcl;
+drop table t1;
+drop table t0;

=== added file 'mysql-test/t/explain_analyze.test'
--- a/mysql-test/t/explain_analyze.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/explain_analyze.test	2012-09-03 15:11:24 +0000
@@ -0,0 +1,28 @@
+#
+# Tests for EXPLAIN ANALYZE
+#
+--disable_warnings
+drop table if exists t0, t1, t2, t3, t4;
+--enable_warnings
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+explain analyze select * from t0 where a<3;
+
+explain
+select * from t0 A where a<3 union select * from t0 A where a > 5;
+
+explain analyze 
+select * from t0 A where a<3 union select * from t0 A where a > 5;
+
+create table t1 (a int, b int);
+insert into t1 select a, a as b from t0;
+
+set @tmp_jcl= @@join_cache_level;
+set join_cache_level=0;
+explain analyze select * from t0, t1 where t0.a=t1.a and t1.b< 3;
+set join_cache_level= @tmp_jcl;
+
+drop table t1;
+drop table t0;

=== modified file 'sql/protocol.h'
--- a/sql/protocol.h	2012-07-10 17:23:00 +0000
+++ b/sql/protocol.h	2012-09-03 15:11:24 +0000
@@ -78,6 +78,20 @@ class Protocol
   virtual bool send_result_set_metadata(List<Item> *list, uint flags);
   bool send_result_set_row(List<Item> *row_items);
 
+  void get_packet(const char **start, size_t *length) 
+  {
+    *start= packet->ptr();
+    *length= packet->length(); 
+  }
+  void set_packet(const char *start, size_t len)
+  {
+    packet->length(0);
+    packet->append(start, len);
+#ifndef DBUG_OFF
+  field_pos= field_count - 1;
+#endif
+  }
+
   bool store(I_List<i_string> *str_list);
   bool store(const char *from, CHARSET_INFO *cs);
   String *storage_packet() { return packet; }

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2012-07-25 16:53:49 +0000
+++ b/sql/sql_class.cc	2012-09-03 15:11:24 +0000
@@ -2015,7 +2015,7 @@ CHANGED_TABLE_LIST* THD::changed_table_d
 int THD::send_explain_fields(select_result *result)
 {
   List<Item> field_list;
-  make_explain_field_list(field_list);
+  make_explain_field_list(field_list, FALSE /* not EXPLAIN ANALYZE */);
   return (result->send_result_set_metadata(field_list,
                                            Protocol::SEND_NUM_ROWS | 
                                            Protocol::SEND_EOF));
@@ -2025,9 +2025,14 @@ int THD::send_explain_fields(select_resu
 /*
   Populate the provided field_list with EXPLAIN output columns.
   this->lex->describe has the EXPLAIN flags
+
+  Column sets in this function must be kept in sync with
+  - print_fake_select_lex_join
+  - JOIN::print_explain
+
 */
 
-void THD::make_explain_field_list(List<Item> &field_list)
+void THD::make_explain_field_list(List<Item> &field_list, bool explain_analyze)
 {
   Item *item;
   CHARSET_INFO *cs= system_charset_info;
@@ -2058,14 +2063,36 @@ void THD::make_explain_field_list(List<I
                                                   NAME_CHAR_LEN*MAX_REF_PARTS,
                                                   cs));
   item->maybe_null=1;
+  if (explain_analyze)
+  {
+    field_list.push_back(item= new Item_return_int("loops", 10,
+                                                 MYSQL_TYPE_LONGLONG));
+    item->maybe_null=1;
+  }
+  
   field_list.push_back(item= new Item_return_int("rows", 10,
                                                  MYSQL_TYPE_LONGLONG));
-  if (lex->describe & DESCRIBE_EXTENDED)
+  item->maybe_null=1;
+
+  if (explain_analyze)
+  {
+    field_list.push_back(item= new Item_return_int("r_rows", 10,
+                                                   MYSQL_TYPE_LONGLONG));
+    item->maybe_null=1;
+  }
+
+  if (lex->describe & DESCRIBE_EXTENDED || explain_analyze)
   {
     field_list.push_back(item= new Item_float("filtered", 0.1234, 2, 4));
     item->maybe_null=1;
   }
-  item->maybe_null= 1;
+
+  if (explain_analyze)
+  {
+    field_list.push_back(item= new Item_float("r_filtered", 0.1234, 2, 4));
+    item->maybe_null=1;
+  }
+
   field_list.push_back(new Item_empty_string("Extra", 255, cs));
 }
 
@@ -2227,6 +2254,11 @@ bool sql_exchange::escaped_given(void)
 bool select_send::send_result_set_metadata(List<Item> &list, uint flags)
 {
   bool res;
+  
+  /* Buypass for SHOW EXPLAIN */
+  if (discard_data)
+    return false;
+
   if (!(res= thd->protocol->send_result_set_metadata(&list, flags)))
     is_result_set_started= 1;
   return res;
@@ -2287,7 +2319,7 @@ int select_send::send_data(List<Item> &i
   ha_release_temporary_latches(thd);
 
   protocol->prepare_for_resend();
-  if (protocol->send_result_set_row(&items))
+  if (!discard_data && protocol->send_result_set_row(&items))
   {
     protocol->remove_last_row();
     DBUG_RETURN(TRUE);
@@ -2295,7 +2327,7 @@ int select_send::send_data(List<Item> &i
 
   thd->sent_row_count++;
 
-  if (thd->vio_ok())
+  if (!discard_data && thd->vio_ok())
     DBUG_RETURN(protocol->write());
 
   DBUG_RETURN(0);
@@ -2323,6 +2355,96 @@ bool select_send::send_eof()
 }
 
 
+//////////////////////////////////////////////////////////////////////////////
+// psergey-explain-analyze: select_result_basic_explain_buffer implementation
+/*
+  Save the data being sent in our internal buffer.
+*/
+
+int select_result_basic_explain_buffer::send_data(List<Item> &items)
+{
+  List_iterator_fast<Item> li(items);
+  char buff[MAX_FIELD_WIDTH];
+  String buffer(buff, sizeof(buff), &my_charset_bin);
+  DBUG_ENTER("select_send::send_data");
+
+  protocol->prepare_for_resend();
+  Item *item;
+  while ((item=li++))
+  {
+    if (item->send(protocol, &buffer))
+    {
+      protocol->free();				// Free used buffer
+      my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+      break;
+    }
+    /*
+      Reset buffer to its original state, as it may have been altered in
+      Item::send().
+    */
+    buffer.set(buff, sizeof(buff), &my_charset_bin);
+  }
+
+  if (thd->is_error())
+  {
+    protocol->remove_last_row();
+    DBUG_RETURN(1);
+  }
+  /* 
+    Instead of calling protocol->write(), steal the packet and put it to our
+    buffer 
+  */
+  const char *packet_data;
+  size_t len;
+  protocol->get_packet(&packet_data, &len);
+
+  String *s= new (thd->mem_root) String;
+  s->append(packet_data, len);
+  data_rows->push_back(s);
+  protocol->remove_last_row(); // <-- this does nothing. Do we need it?
+                               // prepare_for_resend() will wipe out the packet
+  DBUG_RETURN(0);
+}
+
+
+/* Write the saved resultset to the client (via this->protocol) and free it. */
+#if 0
+void select_result_basic_explain_buffer::flush_data()
+{
+  List_iterator<String> it(*data_rows);
+  String *str;
+  while ((str= it++))
+  {
+    protocol->set_packet(str->ptr(), str->length());
+    protocol->write();
+    delete str;
+  }
+  data_rows->empty();
+}
+#endif
+
+/* Free the accumulated resultset */
+
+void select_result_basic_explain_buffer::discard_data()
+{
+  List_iterator<String> it(*data_rows);
+  String *str;
+  while ((str= it++))
+  {
+    delete str;
+  }
+  data_rows->empty();
+}
+
+List<String> *select_result_basic_explain_buffer::remove_stored_data()
+{
+  List<String> *ptr= data_rows;
+  data_rows= new List<String>;
+  return ptr;
+}
+
+//////////////////////////////////////////////////////////////////////////////
+
 /************************************************************************
   Handling writing to file
 ************************************************************************/

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-07-25 16:53:49 +0000
+++ b/sql/sql_class.h	2012-09-03 15:11:24 +0000
@@ -2609,7 +2609,7 @@ class THD :public Statement,
   void add_changed_table(const char *key, long key_length);
   CHANGED_TABLE_LIST * changed_table_dup(const char *key, long key_length);
   int send_explain_fields(select_result *result);
-  void make_explain_field_list(List<Item> &field_list);
+  void make_explain_field_list(List<Item> &field_list, bool explain_analyze);
   /**
     Clear the current error, if any.
     We do not clear is_fatal_error or is_fatal_sub_stmt_error since we
@@ -3323,6 +3323,51 @@ class select_result_explain_buffer : pub
 };
 
 
+/*
+  Backport of:
+
+  A select result sink that collects the sent data and then can flush it to
+  network when requested.
+
+  This class is targeted at collecting EXPLAIN output:
+  - Unoptimized data storage (can't handle big datasets)
+  - Unlike select_result class, we don't assume that the sent data is an 
+    output of a SELECT_LEX_UNIT (and so we dont apply "LIMIT x,y" from the
+    unit)
+
+  (Theoretically, it should be possible to use I_S based one. Discuss this.)
+*/
+
+class select_result_basic_explain_buffer : public select_result_sink
+{
+public:
+  THD *thd;
+  Protocol *protocol;
+  select_result_basic_explain_buffer(){ data_rows= new List<String>; }
+
+  /* The following is called in the child thread: */
+  int send_data(List<Item> &items);
+
+#if 0
+  /* this will be called in the parent thread: */
+  void flush_data();
+#endif 
+
+  void discard_data();
+  void free()
+  { 
+    if (data_rows)
+    {
+      data_rows->delete_elements();
+      delete data_rows;
+    }
+  } 
+
+  List<String> *remove_stored_data();
+
+  List<String> *data_rows;
+};
+
 
 /*
   Base class for select_result descendands which intercept and
@@ -3351,8 +3396,16 @@ class select_send :public select_result 
     set with an eof or error packet
   */
   bool is_result_set_started;
+
 public:
-  select_send() :is_result_set_started(FALSE) {}
+  /*
+    TRUE means discard all of the data, dont touch this->protocol.  This is
+    used by EXPLAIN ANALYZE, where we need to suppress SELECT's output and
+    produce EXPLAIN-type output instead.
+  */
+  bool discard_data;
+
+  select_send() :is_result_set_started(FALSE), discard_data(FALSE) {}
   bool send_result_set_metadata(List<Item> &list, uint flags);
   int send_data(List<Item> &items);
   bool send_eof();

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2012-05-16 20:59:03 +0000
+++ b/sql/sql_join_cache.cc	2012-09-03 15:11:24 +0000
@@ -2056,6 +2056,8 @@ enum_nested_loop_state JOIN_CACHE::join_
   bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
   DBUG_ENTER("JOIN_CACHE::join_records");
 
+  join_tab->loop_count++;
+
   if (outer_join_first_inner && !join_tab->first_unmatched)
     join_tab->not_null_compl= TRUE;   
 

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-07-25 16:53:49 +0000
+++ b/sql/sql_lex.cc	2012-09-03 15:11:24 +0000
@@ -476,6 +476,7 @@ void lex_start(THD *thd)
   if (lex->select_lex.group_list_ptrs)
     lex->select_lex.group_list_ptrs->clear();
   lex->describe= 0;
+  lex->describe_analyze= FALSE;
   lex->subqueries= FALSE;
   lex->context_analysis_only= 0;
   lex->derived_tables= 0;
@@ -1878,6 +1879,7 @@ void st_select_lex::init_query()
   bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used));
   m_non_agg_field_used= false;
   m_agg_func_used= false;
+  saved_explain_plan= NULL;
 }
 
 void st_select_lex::init_select()
@@ -1916,6 +1918,7 @@ void st_select_lex::init_select()
   merged_into= 0;
   m_non_agg_field_used= false;
   m_agg_func_used= false;
+  saved_explain_plan= NULL;
 }
 
 /*
@@ -4162,6 +4165,7 @@ int print_explain_message_line(select_re
 
 int st_select_lex::print_explain(select_result_sink *output, 
                                  uint8 explain_flags,
+                                 bool explain_analyze,
                                  bool *printed_anything)
 {
   int res;
@@ -4177,12 +4181,12 @@ int st_select_lex::print_explain(select_
       /* It's a degenerate join */
       const char *cause= join->zero_result_cause ? join-> zero_result_cause : 
                                                    "No tables used";
-      res= join->print_explain(output, explain_flags, TRUE, FALSE, FALSE, 
-                               FALSE, cause);
+      res= join->print_explain(output, explain_flags, TRUE, explain_analyze,
+                               FALSE, FALSE, FALSE, cause);
     }
     else
     {
-      res= join->print_explain(output, explain_flags, TRUE,
+      res= join->print_explain(output, explain_flags, TRUE, explain_analyze,
                                join->need_tmp, // need_tmp_table
                                !join->skip_sort_order && !join->no_order &&
                                (join->order || join->group_list), // bool need_order
@@ -4192,18 +4196,22 @@ int st_select_lex::print_explain(select_
     if (res)
       goto err;
 
-    for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
-         unit;
-         unit= unit->next_unit())
-    {
-      /* 
-        Display subqueries only if they are not parts of eliminated WHERE/ON
-        clauses.
-      */
-      if (!(unit->item && unit->item->eliminated))
-      {
-        if ((res= unit->print_explain(output, explain_flags, printed_anything)))
-          goto err;
+    if (!explain_analyze)
+    {
+      for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
+           unit;
+           unit= unit->next_unit())
+      {
+        /* 
+          Display subqueries only if they are not parts of eliminated WHERE/ON
+          clauses.
+        */
+        if (!(unit->item && unit->item->eliminated))
+        {
+          /* psergey-todo: note, explain_analyze flag is not passed down into UNIONs */
+          if ((res= unit->print_explain(output, explain_flags, printed_anything)))
+            goto err;
+        }
       }
     }
   }
@@ -4229,6 +4237,38 @@ int st_select_lex::print_explain(select_
 }
 
 
+int st_select_lex::print_saved_explain(THD *thd, select_result_sink *output)
+{
+  int res= 0;
+  List_iterator<String> it(*saved_explain_plan);
+  String *str;
+  while ((str= it++))
+  {
+    thd->protocol->set_packet(str->ptr(), str->length());
+    thd->protocol->write();
+  }
+  saved_explain_plan->delete_elements();
+  delete saved_explain_plan;
+
+  for (SELECT_LEX_UNIT *unit= first_inner_unit();
+       unit;
+       unit= unit->next_unit())
+  {
+    /* 
+      Display subqueries only if they are not parts of eliminated WHERE/ON
+      clauses.
+    */
+    if (!(unit->item && unit->item->eliminated))
+    {
+      if ((res= unit->print_saved_explain(thd, output)))
+        goto err;
+    }
+  }
+err:
+  return res;
+}
+
+
 int st_select_lex_unit::print_explain(select_result_sink *output, 
                                       uint8 explain_flags, bool *printed_anything)
 {
@@ -4249,15 +4289,42 @@ int st_select_lex_unit::print_explain(se
 
   for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
   {
-    if ((res= sl->print_explain(output, explain_flags, printed_anything)))
+    if ((res= sl->print_explain(output, explain_flags, 
+                                /* explain_analyze=*/false, printed_anything)))
+      break;
+  }
+
+  /* Note: fake_select_lex->join may be NULL or non-NULL at this point */
+  if (fake_select_lex)
+  {
+    res= print_fake_select_lex_join(output, TRUE /* on the fly */,
+                                    fake_select_lex, explain_flags, 
+                                    false /* not explain_analyze*/);
+  }
+  return res;
+}
+
+
+int st_select_lex_unit::print_saved_explain(THD *thd, select_result_sink *output)
+{
+  int res= 0;
+  SELECT_LEX *first= first_select();
+  
+  for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
+  {
+    if ((res= sl->print_saved_explain(thd, output)))
       break;
   }
 
   /* Note: fake_select_lex->join may be NULL or non-NULL at this point */
   if (fake_select_lex)
   {
+    res= fake_select_lex->print_saved_explain(thd, output);
+#if 0
     res= print_fake_select_lex_join(output, TRUE /* on the fly */,
-                                    fake_select_lex, explain_flags);
+                                    fake_select_lex, /* explain_flags*/0, 
+                                    /* explain_analyze */ true);
+#endif    
   }
   return res;
 }

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2012-08-08 17:24:00 +0000
+++ b/sql/sql_lex.h	2012-09-03 15:11:24 +0000
@@ -720,6 +720,7 @@ class st_select_lex_unit: public st_sele
   List<Item> *get_unit_column_types();
   int print_explain(select_result_sink *output, uint8 explain_flags,
                     bool *printed_anything);
+  int print_saved_explain(THD *thd, select_result_sink *output);
 };
 
 typedef class st_select_lex_unit SELECT_LEX_UNIT;
@@ -781,6 +782,7 @@ class st_select_lex: public st_select_le
   */
   List<Item_in_subselect> sj_subselects;
   
+  List<String> *saved_explain_plan;
   /*
     Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column
     of EXPLAIN
@@ -1037,7 +1039,9 @@ class st_select_lex: public st_select_le
 
   bool is_merged_child_of(st_select_lex *ancestor);
   int print_explain(select_result_sink *output, uint8 explain_flags, 
+                    bool explain_analyze,
                     bool *printed_anything);
+  int print_saved_explain(THD *thd, select_result_sink *output);
   /*
     For MODE_ONLY_FULL_GROUP_BY we need to maintain two flags:
      - Non-aggregated fields are used in this select.
@@ -2342,6 +2346,7 @@ class Sql_statement : public Sql_alloc
   LEX *m_lex;
 };
 
+class select_result_basic_explain_buffer;
 /* The state of the lex parsing. This is saved in the THD struct */
 
 struct LEX: public Query_tables_list
@@ -2492,6 +2497,10 @@ struct LEX: public Query_tables_list
   */
   uint table_count;
   uint8 describe;
+  bool  describe_analyze;
+  select_result_basic_explain_buffer *explain_analyze_buf;
+
+  List<Item> describe_column_list;
   /*
     A flag that indicates what kinds of derived tables are present in the
     query (0 if no derived tables, otherwise a combination of flags

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2012-08-08 17:24:00 +0000
+++ b/sql/sql_parse.cc	2012-09-03 15:11:24 +0000
@@ -4642,10 +4642,45 @@ static bool execute_sqlcom_select(THD *t
     }
     else
     {
-      if (!result && !(result= new select_send()))
+      select_send* sel_send;
+      if (!result && !(result= sel_send= new select_send()))
         return 1;                               /* purecov: inspected */
       query_cache_store_query(thd, all_tables);
+      /* 
+        Setup EXPLAIN ANALYZE
+        - Switch off production of real query output
+        - Create a sink to save EXPLAIN output
+      */
+      if (lex->describe_analyze)
+      {
+        bool bres;
+
+        lex->explain_analyze_buf= new select_result_basic_explain_buffer;
+        lex->explain_analyze_buf->thd= thd;
+        lex->explain_analyze_buf->protocol= thd->protocol;
+
+        lex->describe_column_list.empty();
+        thd->make_explain_field_list(lex->describe_column_list, true);
+        bres= result->send_result_set_metadata(lex->describe_column_list,
+                                               Protocol::SEND_NUM_ROWS |
+                                               Protocol::SEND_EOF);
+        if (bres)
+          return 1;
+
+        sel_send->discard_data= TRUE;
+      }
+
       res= handle_select(thd, lex, result, 0);
+
+      if (lex->describe_analyze)
+      {
+        /* 
+          Finalize EXPLAIN ANALYZE processing. For now, this just flushes the
+          data. What it should do is to print the output in "explain order"
+         */
+        lex->unit.print_saved_explain(thd, sel_send);
+        lex->explain_analyze_buf->free();
+      }
       if (result != lex->result)
         delete result;
     }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-08-02 13:12:23 +0000
+++ b/sql/sql_select.cc	2012-09-03 15:11:24 +0000
@@ -2252,6 +2252,57 @@ void JOIN::exec()
 }
 
 
+/*********************************************************************************
+ * EXPLAIN ANALYZE 
+ *********************************************************************************/
+
+/*
+  We need to print EXPLAIN ANALYZE after the query is finished.
+
+  The problem is that the runtime will delete parts of the query plan before
+  that point.
+
+  Our solution is 
+  - save every join plan right before it is deleted (this is the point where
+    we have both the query plan and the final values for execution counters)
+  - at query end, print the plan, in the order EXPLAIN would have printed it.
+
+  UNIONs. How to print 'UNION' lines.. with their parent or with their
+  children? With parent, it seems.
+
+  NOTE: we cannot save List<Item> which join->print_explain() passes us because
+  it allocates the list on the stack! Not sure about the items themselves.
+  Probably not, either, because string items use stack-based buffers? 
+
+  Printing joins is organized as follows: 
+  - start_print_join(id)
+    join->print_explain();
+  - end_print_join();
+*/
+
+class Explain_analyze_collector : public Sql_alloc //: public select_result_sink
+{
+public:
+#if 0
+  class Row : public Sql_alloc
+  {
+  public:
+    List <Item> elements;
+  };
+
+  /* Output, in the order it should be printed */
+  List<Row> output;
+  List<Row> unbound_queries;
+  void add(Row *);
+#endif
+  /* This is what JOIN::print_explain will call.*/
+  int send_data(List<Item> &items);
+};
+
+/*********************************************************************************
+ * EXPLAIN ANALYZE END
+ *********************************************************************************/
+
 /**
   Exec select.
 
@@ -10716,6 +10767,22 @@ void JOIN::cleanup(bool full)
   DBUG_ENTER("JOIN::cleanup");
   DBUG_PRINT("enter", ("full %u", (uint) full));
   
+  //psergey-explain-analyze:
+  /*
+    Note that this function can be called multiple times. We need only the
+    first invocation, which doesn't have QEP_DELETED.
+  */
+  if (thd->lex->describe_analyze && (have_query_plan != QEP_DELETED))
+  {
+    bool printed_anything_dummy;
+    select_lex->print_explain(thd->lex->explain_analyze_buf,
+                              DESCRIBE_NORMAL, /*explain_analyze=*/TRUE,
+                              &printed_anything_dummy);
+
+    select_lex->saved_explain_plan=
+    thd->lex->explain_analyze_buf->remove_stored_data();
+  }
+  //:psergey-explain-analyze:
   have_query_plan= QEP_DELETED;
 
   if (table)
@@ -16205,6 +16272,8 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
       (*join_tab->next_select)(join,join_tab+1,end_of_records);
     DBUG_RETURN(nls);
   }
+  join_tab->loop_count++; /* Count this scan */
+
   int error;
   enum_nested_loop_state rc= NESTED_LOOP_OK;
   READ_RECORD *info= &join_tab->read_record;
@@ -16341,6 +16410,8 @@ evaluate_join_record(JOIN *join, JOIN_TA
   if (join_tab->table->vfield)
     update_virtual_fields(join->thd, join_tab->table);
 
+  join_tab->pre_filtered_count++;
+
   if (select_cond)
   {
     select_cond_result= test(select_cond->val_int());
@@ -16357,6 +16428,9 @@ evaluate_join_record(JOIN *join, JOIN_TA
       condition is true => a match is found.
     */
     bool found= 1;
+
+    join_tab->post_filtered_count++;
+
     while (join_tab->first_unmatched && found)
     {
       /*
@@ -21541,8 +21615,10 @@ int print_explain_message_line(select_re
 }
 
 
+/* See THD::make_explain_field_list for column definitions */
 int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
-                               SELECT_LEX *select_lex, uint8 explain_flags)
+                               SELECT_LEX *select_lex, uint8 explain_flags,
+                               bool explain_analyze)
 {
   const CHARSET_INFO *cs= system_charset_info;
   Item *item_null= new Item_null();
@@ -21602,11 +21678,30 @@ int print_fake_select_lex_join(select_re
   item_list.push_back(item_null);
   /* ref */
   item_list.push_back(item_null);
-  /* in_rows */
-  if (explain_flags & DESCRIBE_EXTENDED)
+  if (explain_analyze)
+  {
+    /* loops */
     item_list.push_back(item_null);
+  }
   /* rows */
   item_list.push_back(item_null);
+
+  if (explain_analyze)
+  {
+    /* r_rows */
+    item_list.push_back(item_null);
+  }
+
+  /* filtered */
+  if (explain_flags & DESCRIBE_EXTENDED || explain_analyze)
+    item_list.push_back(item_null);
+
+  if (explain_analyze)
+  {
+    /* r_filtered */
+    item_list.push_back(item_null);
+  }
+
   /* extra */
   if (select_lex->master_unit()->global_parameters->order_list.first)
     item_list.push_back(new Item_string("Using filesort",
@@ -21625,12 +21720,16 @@ int print_fake_select_lex_join(select_re
 
   Produce lines explaining execution of *this* select (not including children
   selects)
+
+  The fields produced must match the column definition produced by 
+  THD::make_explain_field_list.
+
   @param on_the_fly TRUE <=> we're being executed on-the-fly, so don't make 
                     modifications to any select's data structures
 */
 
 int JOIN::print_explain(select_result_sink *result, uint8 explain_flags,
-                         bool on_the_fly,
+                         bool on_the_fly, bool explain_analyze,
                          bool need_tmp_table, bool need_order,
                          bool distinct, const char *message)
 {
@@ -21670,7 +21769,7 @@ int JOIN::print_explain(select_result_si
   {
     if (print_fake_select_lex_join(result, on_the_fly, 
                                    join->select_lex, 
-                                   explain_flags))
+                                   explain_flags, explain_analyze))
       error= 1;
   }
   else if (!join->select_lex->master_unit()->derived ||
@@ -21942,25 +22041,54 @@ int JOIN::print_explain(select_result_si
 	item_list.push_back(item_null);
       }
       
+      /* Add "loops" field to item_list, if needed */
+      if (explain_analyze)
+      {
+        item_list.push_back(new Item_int(tab->loop_count, 
+                                         MY_INT64_NUM_DECIMAL_DIGITS));
+      }
+
       /* Add "rows" field to item_list. */
       if (table_list /* SJM bushes don't have table_list */ &&
           table_list->schema_table)
       {
-        /* in_rows */
-        if (explain_flags & DESCRIBE_EXTENDED)
-          item_list.push_back(item_null);
         /* rows */
         item_list.push_back(item_null);
+        
+        /* r_rows */
+        if (explain_analyze)
+          item_list.push_back(item_null);
+
+        /* filtered */
+        if (explain_flags & DESCRIBE_EXTENDED || explain_analyze)
+          item_list.push_back(item_null);
+        
+        /* r_filtered */
+        if (explain_analyze)
+          item_list.push_back(item_null);
       }
       else
       {
         ha_rows examined_rows= tab->get_examined_rows();
 
+        /* rows */
         item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, 
                                          MY_INT64_NUM_DECIMAL_DIGITS));
 
+        /* r_rows */
+        if (explain_analyze)
+        {
+          if (tab->loop_count)
+          {
+            longlong r_rows= ((double)tab->pre_filtered_count) / tab->loop_count + 0.5;
+            item_list.push_back(new Item_int(r_rows, MY_INT64_NUM_DECIMAL_DIGITS));
+          }
+          else
+            item_list.push_back(item_null);
+        }
+
         /* Add "filtered" field to item_list. */
-        if (explain_flags & DESCRIBE_EXTENDED)
+        if (explain_flags & DESCRIBE_EXTENDED || explain_analyze)
         {
           float f= 0.0; 
           if (examined_rows)
@@ -21968,6 +22096,19 @@ int JOIN::print_explain(select_result_si
  	  set_if_smaller(f, 100.0);
           item_list.push_back(new Item_float(f, 2));
         }
+
+        /* r_filtered */
+        if (explain_analyze)
+        {
+          if (tab->loop_count)
+          {
+            float r_filtered= 100 * ((double)tab->post_filtered_count) /
+                                    ((double)tab->pre_filtered_count);
+            item_list.push_back(new Item_float(r_filtered, 2));
+          }
+          else
+            item_list.push_back(item_null);
+        }
       }
 
       /* Build "Extra" field and add it to item_list. */
@@ -22209,6 +22350,7 @@ static void select_describe(JOIN *join, 
   DBUG_ENTER("select_describe");
   join->error= join->print_explain(result, thd->lex->describe, 
                                    FALSE, /* Not on-the-fly */
+                                   FALSE, /* Not EXPLAIN ANALYZE */
                                    need_tmp_table, need_order, distinct, 
                                    message);
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-07-25 16:53:49 +0000
+++ b/sql/sql_select.h	2012-09-03 15:11:24 +0000
@@ -412,6 +412,21 @@ typedef struct st_join_table {
   uint n_sj_tables;
 
   bool preread_init_done;
+  /* 
+    psergey-explain-analyze: the number of times a scan was done on this
+    join_tab.
+  */
+  ulonglong loop_count;
+
+  /*
+    How many output records it has produced
+
+     r_rows=  AVG(access method fanout) = pre_filtered_count/ loop_count
+     r_filtered = AVG(tab's fanout) = post_filtered_count / loop_count 
+
+  */
+  ulonglong pre_filtered_count;
+  ulonglong post_filtered_count;
 
   void cleanup();
   inline bool is_using_loose_index_scan()
@@ -1432,7 +1447,7 @@ class JOIN :public Sql_alloc
   }
 
   int print_explain(select_result_sink *result, uint8 explain_flags,
-                     bool on_the_fly,
+                     bool on_the_fly, bool explain_analyze,
                      bool need_tmp_table, bool need_order,
                      bool distinct,const char *message);
 private:
@@ -1787,7 +1802,8 @@ inline bool optimizer_flag(THD *thd, uin
 }
 
 int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
-                               SELECT_LEX *select_lex, uint8 select_options);
+                               SELECT_LEX *select_lex, uint8 select_options,
+                               bool explain_analyze);
 
 uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select,
                          ha_rows limit, bool *need_sort, bool *reverse);

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2012-08-08 17:24:00 +0000
+++ b/sql/sql_yacc.yy	2012-09-03 15:11:24 +0000
@@ -11702,11 +11702,15 @@ select_var_ident:  
           }
           opt_describe_column {}
         | describe_command opt_extended_describe
-          { Lex->describe|= DESCRIBE_NORMAL; }
+          { 
+            if (!Lex->describe_analyze)
+              Lex->describe|= DESCRIBE_NORMAL; 
+          }
           select
           {
             LEX *lex=Lex;
-            lex->select_lex.options|= SELECT_DESCRIBE;
+            if (!lex->describe_analyze)
+              lex->select_lex.options|= SELECT_DESCRIBE;
           }
         ;
 
@@ -11719,6 +11723,7 @@ select_var_ident:  
           /* empty */ {}
         | EXTENDED_SYM   { Lex->describe|= DESCRIBE_EXTENDED; }
         | PARTITIONS_SYM { Lex->describe|= DESCRIBE_PARTITIONS; }
+        | ANALYZE_SYM { Lex->describe_analyze= TRUE; }
         ;
 
 opt_describe_column:



More information about the commits mailing list