[Commits] Rev 3394: Merge 5.3-main -> 5.3-optimizer-debugging in file:///home/psergey/dev2/5.3-look52/

Sergey Petrunya psergey at askmonty.org
Tue Jan 24 18:13:40 EET 2012


At file:///home/psergey/dev2/5.3-look52/

------------------------------------------------------------
revno: 3394 [merge]
revision-id: psergey at askmonty.org-20120124161330-ele7oc6hipwhnhpd
parent: sanja at askmonty.org-20120123131413-uv5t0muz46ukwy2u
parent: psergey at askmonty.org-20111228082953-1bgk2swjxlpghw35
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look52
timestamp: Tue 2012-01-24 20:13:30 +0400
message:
  Merge 5.3-main -> 5.3-optimizer-debugging
added:
  mysql-test/include/have_optimizer_debugging.inc have_optimizer_debug-20111201163149-rqta5hoa5lrd4r5x-1
  mysql-test/r/debug_optimizer.result debug_optimizer.resu-20111201163153-k58bersvettuzwak-1
  mysql-test/r/have_optimizer_debugging.require have_optimizer_debug-20111201163157-uzkselcokn703wui-1
  mysql-test/t/debug_optimizer.test debug_optimizer.test-20111201163200-c3rmx74bafh4uecm-1
modified:
  sql/mysql_priv.h               sp1f-mysql_priv.h-19700101030959-4fl65tqpop5zfgxaxkqotu2fa2ree5ci
  sql/mysqld.cc                  sp1f-mysqld.cc-19700101030959-zpswdvekpvixxzxf7gdtofzel7nywtfj
  sql/opt_subselect.cc           opt_subselect.cc-20100215190428-nekkl8wisp0k6nlk-1
  sql/set_var.cc                 sp1f-set_var.cc-20020723153119-nwbpg2pwpz55pfw7yfzaxt7hsszzy7y3
  sql/set_var.h                  sp1f-set_var.h-20020723153119-2yomygq3s4xjbqvuue3cdlpbjtj3kwmk
  sql/sql_class.cc               sp1f-sql_class.cc-19700101030959-rpotnweaff2pikkozh3butrf7mv3oero
  sql/sql_class.h                sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
=== added file 'mysql-test/include/have_optimizer_debugging.inc'
--- a/mysql-test/include/have_optimizer_debugging.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/have_optimizer_debugging.inc	2011-12-01 16:33:21 +0000
@@ -0,0 +1,8 @@
+## --require r/have_optimizer_debugging.require
+--disable_query_log
+let $a= `select 1 from information_schema.session_variables where variable_name like 'debug_optimizer_prefer_join_prefix'`;
+--enable_query_log
+if (!$a)
+{
+  skip Server is not compiled with #define DEBUG_OPTIMIZER
+}

=== added file 'mysql-test/r/debug_optimizer.result'
--- a/mysql-test/r/debug_optimizer.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/debug_optimizer.result	2011-12-01 16:33:21 +0000
@@ -0,0 +1,63 @@
+drop table if exists t0, t1, t2, t3;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select * from t0;
+create table t2 as select * from t0;
+explain select * from t0, t1, t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+#
+#  Check that we can force join prefix
+#
+set debug_optimizer_prefer_join_prefix='t2,t1,t0';
+explain select * from t0, t1, t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+set debug_optimizer_prefer_join_prefix='t1,t0,t2';
+explain select * from t0, t1, t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+set debug_optimizer_prefer_join_prefix='C,B,A';
+explain select * from t0 C, t1 B, t2 A;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	C	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+set debug_optimizer_prefer_join_prefix=NULL;
+#
+#  Check that duplicate elimination can be disabled (note: to disable it 
+#    fully, one need to have optimizer_prune_level=0
+#
+drop table t1, t2;
+create table t2 (a int, b int, c int);
+create table t3 (a int, b int, c int);
+insert into t2 select A.a, A.a, A.a from t0 A, t0 B;
+insert into t3 select A.a, A.a, A.a from t0 A;
+alter table t2 add key(a);
+# This is a query that is run with Duplicate Elimination
+explain select * from t2 A where a in (select C1.a + C2.a from t3 C1, t3 C2 where C1.b<A.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	C1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	C2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	A	ref	a	a	5	func	10	Using index condition; Using where; End temporary
+set optimizer_prune_level=0;
+# Check that setting optimizer_prune_level still gives Duplicate Elimination:
+explain select * from t2 A where a in (select C1.a + C2.a from t3 C1, t3 C2 where C1.b<A.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	C1	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	C2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	A	ref	a	a	5	func	10	Using index condition; Using where; End temporary
+# Now, turn on Duplicate Weedout penalty and see that it is no longer used:
+set debug_optimizer_dupsweedout_penalized=ON;
+explain select * from t2 A where a in (select C1.a + C2.a from t3 C1, t3 C2 where C1.b<A.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	a	NULL	NULL	NULL	100	
+1	PRIMARY	C2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	C1	ALL	NULL	NULL	NULL	NULL	10	Using where; FirstMatch(A)
+drop table t0, t2, t3;

=== added file 'mysql-test/r/have_optimizer_debugging.require'
--- a/mysql-test/r/have_optimizer_debugging.require	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/have_optimizer_debugging.require	2011-12-01 16:33:21 +0000
@@ -0,0 +1,2 @@
+1
+1

=== added file 'mysql-test/t/debug_optimizer.test'
--- a/mysql-test/t/debug_optimizer.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/debug_optimizer.test	2011-12-01 16:33:21 +0000
@@ -0,0 +1,54 @@
+#
+# Optimizer debugging tests
+#
+--source include/have_optimizer_debugging.inc
+
+--disable_warnings
+drop table if exists t0, t1, t2, t3;
+--enable_warnings
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 as select * from t0;
+create table t2 as select * from t0;
+
+explain select * from t0, t1, t2;
+
+--echo #
+--echo #  Check that we can force join prefix
+--echo #
+set debug_optimizer_prefer_join_prefix='t2,t1,t0';
+explain select * from t0, t1, t2;
+
+set debug_optimizer_prefer_join_prefix='t1,t0,t2';
+explain select * from t0, t1, t2;
+
+set debug_optimizer_prefer_join_prefix='C,B,A';
+explain select * from t0 C, t1 B, t2 A;
+
+set debug_optimizer_prefer_join_prefix=NULL;
+
+--echo #
+--echo #  Check that duplicate elimination can be disabled (note: to disable it 
+--echo #    fully, one need to have optimizer_prune_level=0
+--echo #
+drop table t1, t2;
+create table t2 (a int, b int, c int);
+create table t3 (a int, b int, c int);
+
+insert into t2 select A.a, A.a, A.a from t0 A, t0 B;
+insert into t3 select A.a, A.a, A.a from t0 A;
+
+alter table t2 add key(a);
+--echo # This is a query that is run with Duplicate Elimination
+explain select * from t2 A where a in (select C1.a + C2.a from t3 C1, t3 C2 where C1.b<A.b);
+set optimizer_prune_level=0;
+--echo # Check that setting optimizer_prune_level still gives Duplicate Elimination:
+explain select * from t2 A where a in (select C1.a + C2.a from t3 C1, t3 C2 where C1.b<A.b);
+
+--echo # Now, turn on Duplicate Weedout penalty and see that it is no longer used:
+set debug_optimizer_dupsweedout_penalized=ON;
+explain select * from t2 A where a in (select C1.a + C2.a from t3 C1, t3 C2 where C1.b<A.b);
+
+drop table t0, t2, t3;

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2011-12-15 08:21:15 +0000
+++ b/sql/mysql_priv.h	2011-12-28 08:29:53 +0000
@@ -30,6 +30,7 @@
 
 #ifndef MYSQL_CLIENT
 
+#define DEBUG_OPTIMIZER 1
 /*
   the following #define adds server-only members to enum_mysql_show_type,
   that is defined in mysql/plugin.h

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2012-01-13 12:35:49 +0000
+++ b/sql/mysqld.cc	2012-01-24 16:13:30 +0000
@@ -6138,6 +6138,10 @@
   OPT_OPTIMIZER_SEARCH_DEPTH,
   OPT_OPTIMIZER_PRUNE_LEVEL,
   OPT_OPTIMIZER_SWITCH,
+#ifdef DEBUG_OPTIMIZER
+  OPT_DEBUG_OPTIMIZER_DUPSWEEDOUT_PENALIZED,
+  OPT_DEBUG_OPTIMIZER_PREFER_JOIN_PREFIX,
+#endif
   OPT_UPDATABLE_VIEWS_WITH_LIMIT,
   OPT_SP_AUTOMATIC_PRIVILEGES,
   OPT_MAX_SP_RECURSION_DEPTH,
@@ -6195,6 +6199,11 @@
 
 #define LONG_TIMEOUT ((ulong) 3600L*24L*365L)
 
+#ifdef DEBUG_OPTIMIZER
+char *opt_preferred_join_prefix= (char*)"";
+char **parse_preferred_join_prefix(char *prefix_line, size_t len);
+#endif
+
 struct my_option my_long_options[] =
 {
   {"help", '?', "Display this help and exit.", 
@@ -7594,6 +7603,17 @@
    "} and val={on, off, default}.",
    &optimizer_switch_str, &optimizer_switch_str, 0, GET_STR, REQUIRED_ARG, 
    /*OPTIMIZER_SWITCH_DEFAULT*/0, 0, 0, 0, 0, 0},
+#ifdef DEBUG_OPTIMIZER
+  {"debug_optimizer_dupsweedout_penalized", OPT_DEBUG_OPTIMIZER_DUPSWEEDOUT_PENALIZED,
+   "DEBUG MODE ONLY: Set to 1 to have Duplicate Weedout strategy penalized",
+   &global_system_variables.debug_optimizer_dupsweedout_penalized,
+   &max_system_variables.debug_optimizer_dupsweedout_penalized,
+   0, GET_BOOL, OPT_ARG, 0, 0, 1, 0, 1, 0},
+  {"debug_optimizer_prefer_join_prefix", OPT_DEBUG_OPTIMIZER_PREFER_JOIN_PREFIX,
+   "DEBUG MODE ONLY: Prefer this join prefix",
+   &opt_preferred_join_prefix, &opt_preferred_join_prefix, 0,
+   GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+#endif 
   {"plugin_dir", OPT_PLUGIN_DIR,
    "Directory for plugins.",
    &opt_plugin_dir_ptr, &opt_plugin_dir_ptr, 0,
@@ -8647,6 +8667,10 @@
   global_system_variables.myisam_stats_method= MI_STATS_METHOD_NULLS_NOT_EQUAL;
   
   global_system_variables.optimizer_switch= OPTIMIZER_SWITCH_DEFAULT;
+#ifdef DEBUG_OPTIMIZER  
+  global_system_variables.debug_optimizer_prefer_join_prefix= NULL; // Actually set in get_options
+  global_system_variables.debug_optimizer_dupsweedout_penalized= 0;
+#endif
   /* Variables that depends on compile options */
 #ifndef DBUG_OFF
   default_dbug_option=IF_WIN("d:t:i:O,\\mysqld.trace",
@@ -9642,7 +9666,11 @@
       init_global_datetime_format(MYSQL_TIMESTAMP_DATETIME,
 				  &global_system_variables.datetime_format))
     return 1;
-
+#ifdef DEBUG_OPTIMIZER
+  uint prefix_len= strlen(opt_preferred_join_prefix);
+  global_system_variables.debug_optimizer_prefer_join_prefix=
+    parse_preferred_join_prefix(opt_preferred_join_prefix, prefix_len);
+#endif 
 #ifdef SAFEMALLOC
   sf_malloc_quick= !sf_malloc_trough_check;
 #endif

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-01-19 19:44:43 +0000
+++ b/sql/opt_subselect.cc	2012-01-24 16:13:30 +0000
@@ -2776,7 +2776,11 @@
                              sj_outer_fanout* sj_inner_fanout * 
                              one_lookup_cost;
     dups_cost += write_cost + full_lookup_cost;
-    
+
+#ifdef DEBUG_OPTIMIZER
+    if (join->thd->variables.debug_optimizer_dupsweedout_penalized)
+      dups_cost *= 1000.0*1000.0;
+#endif
     *read_time= dups_cost;
     *record_count= prefix_rec_count * sj_outer_fanout;
     *handled_fanout= dups_removed_fanout;

=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc	2011-12-15 12:43:28 +0000
+++ b/sql/set_var.cc	2011-12-28 08:29:53 +0000
@@ -532,6 +532,13 @@
                                                    &SV::optimizer_search_depth);
 static sys_var_thd_optimizer_switch   sys_optimizer_switch(&vars, "optimizer_switch",
                                      &SV::optimizer_switch);
+#ifdef DEBUG_OPTIMIZER
+sys_var_thd_bool 
+sys_debug_optimizer_dupsweedout_penalized(&vars, "debug_optimizer_dupsweedout_penalized",
+                                          &SV::debug_optimizer_dupsweedout_penalized);
+sys_var_thd_preferred_join_prefix
+sys_debug_optimizer_preferred_join_prefix(&vars, "debug_optimizer_prefer_join_prefix");
+#endif
 
 static sys_var_thd_ulong        sys_progress_report_time(&vars,
                                                          "progress_report_time",
@@ -4236,6 +4243,188 @@
     thd->variables.*offset= global_system_variables.*offset;
 }
 
+#ifdef DEBUG_OPTIMIZER
+
+void free_preferred_prefix(char **prefix)
+{
+  if (prefix)
+  {
+    for (char **p= prefix; *p; p++)
+      my_free(*p, MYF(0));
+    my_free(prefix, MYF(0));
+  }
+}
+
+char** copy_preferred_prefix(char **prefix)
+{
+  if (prefix == NULL)
+    return NULL;
+
+  char **p;
+  for (p= prefix; *p; p++) {}
+  int len= p - prefix + 1;
+  char **result;
+  if (!(result= (char**)my_malloc(len * sizeof(prefix[0]), MYF(0))))
+    goto err_free;
+  bzero(result, len * sizeof(prefix[0]));
+  char **p_copy;
+  for (p= prefix, p_copy= result; *p; p++, p_copy++)
+  {
+    char *copy;
+    if (!(copy= my_strdup(*p, MYF(0))))
+      goto err_free;
+    *p_copy= copy;
+  }
+  *p_copy= NULL;
+
+  return result;
+err_free:
+  if (result)
+  {
+    for (char **p_res= result; *p_res; p_res++)
+      free(*p_res);
+    free(result);
+  }
+  return NULL;
+}
+
+
+char **parse_preferred_join_prefix(char *prefix_line, size_t len)
+{
+  char **parsed_prefix;
+  char *table_names[MAX_TABLES];
+  int idx=0;
+  
+  if (len == 0)
+    return NULL;
+
+  // Parse the comma-separated list and create array-of-pointers structure
+  memset(table_names, 0, sizeof(table_names));
+  for (char *p= prefix_line; *p && idx < (int)MAX_TABLES;)
+  {
+    // Find the next comma
+    char *name_end;
+    for (name_end= p; *name_end && *name_end !=','; name_end++) {}
+    char *p_name;
+    if (!(p_name= (char*)my_malloc(name_end - p + 1, MYF(0))))
+      goto error_free;
+    memcpy(p_name, p, name_end - p + 1);
+    p_name[name_end - p]=0;
+    table_names[idx++]= p_name;
+    p= name_end;
+    if (*p == ',')
+      p++;
+  }
+  table_names[idx]= NULL;
+
+  if (!(parsed_prefix= (char**)my_malloc(sizeof(table_names[0]) * (idx + 1), MYF(0))))
+    goto error_free;
+  memcpy(parsed_prefix, table_names, sizeof(table_names[0]) * (idx+1));
+
+  return parsed_prefix;
+error_free:
+  free_preferred_prefix(table_names);
+  return NULL;
+}
+
+
+void sys_var_thd_preferred_join_prefix::set_default(THD *thd, enum_var_type type)
+{
+  if (type == OPT_GLOBAL)
+  {
+    free_preferred_prefix(global_system_variables.debug_optimizer_prefer_join_prefix);
+    global_system_variables.debug_optimizer_prefer_join_prefix= NULL;
+  }
+  else
+  {
+    thd->variables.debug_optimizer_prefer_join_prefix=
+      copy_preferred_prefix(global_system_variables.debug_optimizer_prefer_join_prefix);
+  }
+}
+
+
+bool sys_var_thd_preferred_join_prefix::check(THD *thd, set_var *var)
+{
+  //psergey-todo: nothing to do, anything is a good prefix.
+  return FALSE;
+}
+
+
+bool sys_var_thd_preferred_join_prefix::update(THD *thd, set_var *var)
+{
+  String str;
+  String *res;
+  
+  char ***p_prefix;
+  if (var->type == OPT_GLOBAL)
+    p_prefix= &global_system_variables.debug_optimizer_prefer_join_prefix;
+  else
+    p_prefix= &thd->variables.debug_optimizer_prefer_join_prefix;
+
+  char **parsed_prefix;
+  if (!(res= var->value->val_str(&str)) || res->length() == 0)
+  {
+    /* It's a NULL */
+    parsed_prefix= NULL;
+  }
+  else
+    parsed_prefix= parse_preferred_join_prefix(res->c_ptr(),res->length());
+
+  free_preferred_prefix(*p_prefix);
+  *p_prefix= parsed_prefix;
+  return FALSE;
+}
+
+
+uchar *sys_var_thd_preferred_join_prefix::value_ptr(THD *thd, enum_var_type type,
+				                    LEX_STRING *base)
+{
+  char *prefer_line;
+  char *cur_ptr;
+  size_t prefer_line_len;
+  char **prefix_val= (type == OPT_GLOBAL) ? 
+                      global_system_variables.debug_optimizer_prefer_join_prefix:
+                      thd->variables.debug_optimizer_prefer_join_prefix;
+  if (!prefix_val)
+    goto empty_val;
+
+  /* 
+    Calculate how much space will be required to construct the line in form:
+      name1,name2,...nameN\0 
+  */
+  char **p;
+  prefer_line_len= 0;
+  for (p=prefix_val; *p; p++)
+    prefer_line_len += strlen(*p) + 1;
+  
+  /* Allocate memory and construct the line */
+  if (!(prefer_line= (char *) thd->alloc(prefer_line_len)))
+    goto empty_val;
+
+  cur_ptr= prefer_line;
+  for (p=prefix_val; *p; p++)
+  {
+    size_t len= strlen(*p);
+    memcpy(cur_ptr, *p, len);
+    cur_ptr += len;
+    if (p[1])
+      *cur_ptr= ',';
+    else
+      *cur_ptr= 0;
+    cur_ptr++;
+  }
+  base->str= prefer_line;
+  base->length= cur_ptr - prefer_line;
+  return (uchar *) prefer_line;
+
+empty_val:
+  base->str= NULL;
+  base->length= 0;
+  return NULL;
+}
+
+
+#endif
 /****************************************************************************
   Named list handling
 ****************************************************************************/

=== modified file 'sql/set_var.h'
--- a/sql/set_var.h	2011-12-11 09:34:44 +0000
+++ b/sql/set_var.h	2011-12-28 08:29:53 +0000
@@ -567,6 +567,39 @@
                                            LEX_STRING *rep);
 };
 
+#ifdef DEBUG_OPTIMIZER
+class sys_var_thd_preferred_join_prefix : public sys_var_thd
+{
+public:
+  sys_var_thd_preferred_join_prefix(sys_var_chain *chain, const char *name_arg)
+    :sys_var_thd(name_arg, NULL)
+  { chain_sys_var(chain); }
+
+  bool check_update_type(Item_result type)
+  {
+    return type != STRING_RESULT;		/* Only accept strings */
+  }
+
+  SHOW_TYPE show_type() { return SHOW_CHAR; }
+  
+  void set_default(THD *thd, enum_var_type type);
+  bool check(THD *thd, set_var *var);
+
+  bool update(THD *thd, set_var *var);
+  uchar *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+};
+
+/*
+class sys_var_thd_preferred_join_prefix :public sys_var_thd
+{
+  ...
+  bool check_default(enum_var_type type) { return 0; }
+  void update2(THD *thd, enum_var_type type, struct st_preferred_join_prefix *new_value);
+};
+*/
+
+#endif
+
 extern void fix_sql_mode_var(THD *thd, enum_var_type type);
 
 class sys_var_thd_sql_mode :public sys_var_thd_set

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2011-12-11 16:39:33 +0000
+++ b/sql/sql_class.cc	2011-12-28 08:29:53 +0000
@@ -912,6 +912,11 @@
   return my_pthread_getspecific_ptr(THD*,THR_THD);
 }
 #endif
+
+#ifdef DEBUG_OPTIMIZER
+char** copy_preferred_prefix(char **prefix);
+#endif
+
 /*
   Init common variables that has to be reset on start and on change_user
 */
@@ -926,6 +931,11 @@
 					       variables.date_format);
   variables.datetime_format= date_time_format_copy((THD*) 0,
 						   variables.datetime_format);
+
+#ifdef DEBUG_OPTIMIZER
+   variables.debug_optimizer_prefer_join_prefix=
+     copy_preferred_prefix(variables.debug_optimizer_prefer_join_prefix);
+#endif
   /*
     variables= global_system_variables above has reset
     variables.pseudo_thread_id to 0. We need to correct it here to
@@ -1115,7 +1125,9 @@
   my_free((char*) variables.time_format, MYF(MY_ALLOW_ZERO_PTR));
   my_free((char*) variables.date_format, MYF(MY_ALLOW_ZERO_PTR));
   my_free((char*) variables.datetime_format, MYF(MY_ALLOW_ZERO_PTR));
-
+#ifdef DEBUG_OPTIMIZER
+//psergey-todo:
+#endif
   sp_cache_clear(&sp_proc_cache);
   sp_cache_clear(&sp_func_cache);
 

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-01-13 12:35:49 +0000
+++ b/sql/sql_class.h	2012-01-24 16:13:30 +0000
@@ -458,6 +458,18 @@
   ulong optimizer_search_depth;
   /* A bitmap for switching optimizations on/off */
   ulong optimizer_switch;
+#ifdef DEBUG_OPTIMIZER
+  /*
+    TRUE <=> DuplicateWeedout strategy will be heavily penalized by multiplying
+    its costs by 1 million
+  */
+  my_bool debug_optimizer_dupsweedout_penalized;
+  /*
+    A NULL-terminated array of char* table names of the "preferred" join
+    prefix. Join prefixes that do not match the pattern are penalized
+  */
+  char **debug_optimizer_prefer_join_prefix; 
+#endif
   ulong preload_buff_size;
   ulong profiling_history_size;
   ulong query_cache_type;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-19 22:11:53 +0000
+++ b/sql/sql_select.cc	2012-01-24 16:13:30 +0000
@@ -6230,6 +6230,33 @@
   *record_count_arg= record_count;
 }
 
+#ifdef DEBUG_OPTIMIZER
+static void discriminate_join_prefix(JOIN *join, POSITION *pos, uint idx,
+                                     double *current_read_time)
+{
+  char **prefix;
+  if (!join->emb_sjm_nest && 
+      (prefix= join->thd->variables.debug_optimizer_prefer_join_prefix))
+  {
+    /*
+      Check if prefix in pos matches the one 
+    */
+    bool match= TRUE;
+    for (uint i=0; i <=idx && *prefix ; i++, prefix++)
+    {
+      if (my_strcasecmp(table_alias_charset, *prefix,
+                        pos[i].table->table->alias.ptr()))
+      {
+        match= FALSE;
+        break;
+      }
+    }
+
+    if (!match)
+      *current_read_time *= 1000.0 * 1000.0;
+  }
+}
+#endif 
 
 /**
   Find a good, possibly optimal, query execution plan (QEP) by a possibly
@@ -6410,6 +6437,9 @@
 
       advance_sj_state(join, remaining_tables, idx, &current_record_count,
                        &current_read_time, &loose_scan_pos);
+#ifdef DEBUG_OPTIMIZER
+      discriminate_join_prefix(join, join->positions, idx, &current_read_time);
+#endif
 
       /* Expand only partial plans with lower cost than the best QEP so far */
       if (current_read_time >= join->best_read)
@@ -6563,7 +6593,9 @@
       double current_read_time=read_time+best;
       advance_sj_state(join, rest_tables, idx, &current_record_count, 
                        &current_read_time, &loose_scan_pos);
-
+#ifdef DEBUG_OPTIMIZER
+      discriminate_join_prefix(join, join->positions, idx, &current_read_time);
+#endif
       if (best_record_count > current_record_count ||
 	  best_read_time > current_read_time ||
 	  (idx == join->const_tables && s->table == join->sort_by_table))



More information about the commits mailing list