#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/ based on revid:jorgen.loland@stripped
3253 Jorgen Loland 2011-01-14
WL#5741: Add optimizer tracing to subqueries
* Fix review comments by Guilhem
* Update copyright notice.
modified:
WL4800_TODO.txt
mysql-test/r/mysqld--help-notwin.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/r/optimizer_trace_range.result
mysql-test/r/optimizer_trace_subquery.result
mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result
sql/item_subselect.cc
sql/item_subselect.h
sql/opt_range.cc
sql/opt_range.h
sql/opt_trace.cc
sql/opt_trace.h
sql/sql_select.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt 2011-01-12 13:44:58 +0000
+++ b/WL4800_TODO.txt 2011-01-14 15:53:48 +0000
@@ -32,10 +32,6 @@ fix all crashes of --opt-trace-protocol
try to avoid the call to get_current_struct in opt_range.cc
-solve the problems of "one trace per row scanned" ("range checked for
-each record" and subqueries); one example is at the end of
-http://lists.mysql.com/commits/124599
-
check save_in_field_no_warnings() in opt_range.cc: do we need to
disable tracing there? see optimizer_trace_no_prot.result. Jorgen has
maybe looked at this in his in-review subquery tracing patch.
@@ -53,3 +49,5 @@ range access anyway.
Make --opt-trace-protocol dump traces to a separate file so that mtr
can run with it without failing all tests.
+
+Update copyright header of all changed files to reflect changed in 2011
=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result 2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result 2011-01-14 15:53:48 +0000
@@ -872,7 +872,7 @@ optimizer-prune-level 1
optimizer-search-depth 62
optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
optimizer-trace
-optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on
+optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer-trace-limit 1
optimizer-trace-max-mem-size 16384
optimizer-trace-offset -1
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2011-01-14 15:53:48 +0000
@@ -2832,7 +2832,7 @@ explain select * from t1,t2 {
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -3515,6 +3515,34 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
}
] /* steps */
} /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
}
] /* steps */
} /* join_execution */
@@ -4977,6 +5005,20 @@ select * from t1 where (t1.a,t1.b) not i
}
] /* steps */
} /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
}
] /* steps */
} /* join_execution */
=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result 2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2011-01-14 15:53:48 +0000
@@ -2816,7 +2816,7 @@ explain select * from t1,t2 {
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -3499,6 +3499,34 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
}
] /* steps */
} /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
}
] /* steps */
} /* join_execution */
@@ -4953,6 +4981,20 @@ select * from t1 where (t1.a,t1.b) not i
}
] /* steps */
} /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
}
] /* steps */
} /* join_execution */
=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result 2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_range.result 2011-01-14 15:53:48 +0000
@@ -3450,6 +3450,40 @@ EXPLAIN SELECT 1 FROM
"join_execution": {
"select#": 2,
"steps": [
+ {
+ "records_estimation_per_record": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */
+ } /* records_estimation_per_record */
+ }
] /* steps */
} /* join_execution */
},
@@ -3735,6 +3769,20 @@ SELECT * from t1 where topic = all (SELE
}
] /* steps */
} /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
}
] /* steps */
} /* join_execution */
=== modified file 'mysql-test/r/optimizer_trace_subquery.result'
--- a/mysql-test/r/optimizer_trace_subquery.result 2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_subquery.result 2011-01-14 15:53:48 +0000
@@ -413,6 +413,13 @@ t1.a= (SELECT a FROM t2 LIMIT 1) {
}
] /* steps */
} /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* subselect_execution */
}
] /* subselect_equality_propagation */,
"resulting_condition": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))"
=== modified file 'mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result 2011-01-14 13:42:35 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result 2011-01-14 15:53:48 +0000
@@ -1,25 +1,25 @@
SET @start_global_value = @@global.optimizer_trace_features;
SELECT @start_global_value;
@start_global_value
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
select @@session.optimizer_trace_features;
@@session.optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
show global variables like 'optimizer_trace_features';
Variable_name Value
-optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
show session variables like 'optimizer_trace_features';
Variable_name Value
-optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
select * from information_schema.global_variables where variable_name='optimizer_trace_features';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
select * from information_schema.session_variables where variable_name='optimizer_trace_features';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+OPTIMIZER_TRACE_FEATURES greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
set global optimizer_trace_features=2;
select @@global.optimizer_trace_features;
@@global.optimizer_trace_features
@@ -49,4 +49,4 @@ ERROR 42000: Variable 'optimizer_trace_f
SET @@global.optimizer_trace_features = @start_global_value;
SELECT @@global.optimizer_trace_features;
@@global.optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2011-01-14 13:42:35 +0000
+++ b/sql/item_subselect.cc 2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2011-01-14 13:42:35 +0000
+++ b/sql/item_subselect.h 2011-01-14 15:53:48 +0000
@@ -1,7 +1,7 @@
#ifndef ITEM_SUBSELECT_INCLUDED
#define ITEM_SUBSELECT_INCLUDED
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-01-14 13:42:35 +0000
+++ b/sql/opt_range.cc 2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -1152,7 +1152,9 @@ SQL_SELECT *make_select(TABLE *head, tab
}
-SQL_SELECT::SQL_SELECT() :quick(0),cond(0),icp_cond(0),free_cond(0)
+SQL_SELECT::SQL_SELECT() :
+ quick(0), cond(0), icp_cond(0),
+ free_cond(0), traced_before(false)
{
my_b_clear(&file);
}
@@ -1169,6 +1171,7 @@ void SQL_SELECT::cleanup()
cond= 0;
}
close_cached_file(&file);
+ traced_before= false;
}
@@ -2588,9 +2591,6 @@ int SQL_SELECT::test_quick_select(THD *t
if (cond)
{
{
- //Guilhem: this is needed because we might evaluate a subquery
- //inside get_mm_tree, but if you have a better suggestion I'm
- //all ears. I don't like this tracepoint very much.
Opt_trace_array trace_setup_cond(trace, "setup_range_conditions");
tree= get_mm_tree(¶m,cond);
}
=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h 2011-01-03 13:10:02 +0000
+++ b/sql/opt_range.h 2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -856,6 +856,14 @@ class SQL_SELECT :public Sql_alloc {
table_map const_tables,read_tables;
bool free_cond;
+ /**
+ Used by optimizer tracing if this is a subquery: Whether or not
+ execution of this subselect has been traced by optimizer tracing
+ already. If optimizer trace option DYNAMIC_RANGE is disabled,
+ this is used to disable tracing after the first one.
+ */
+ bool traced_before;
+
SQL_SELECT();
~SQL_SELECT();
void cleanup();
=== modified file 'sql/opt_trace.cc'
--- a/sql/opt_trace.cc 2011-01-14 13:42:35 +0000
+++ b/sql/opt_trace.cc 2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2009, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -386,9 +386,10 @@ const char *Opt_trace_context::feature_n
};
const Opt_trace_context::feature_value Opt_trace_context::FEATURES_DEFAULT=
- Opt_trace_context::feature_value(Opt_trace_context::GREEDY_SEARCH |
- Opt_trace_context::DYNAMIC_RANGE |
- Opt_trace_context::RANGE_OPTIMIZER);
+ Opt_trace_context::feature_value(Opt_trace_context::GREEDY_SEARCH |
+ Opt_trace_context::RANGE_OPTIMIZER |
+ Opt_trace_context::DYNAMIC_RANGE |
+ Opt_trace_context::REPEATED_SUBSELECT);
Opt_trace_context::Opt_trace_context(void):
oldest_stmt_to_show(NULL), newest_stmt_to_show(NULL), stmt_to_del(NULL),
=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h 2011-01-14 13:42:35 +0000
+++ b/sql/opt_trace.h 2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2009, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-01-14 13:42:35 +0000
+++ b/sql/sql_select.cc 2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -18436,10 +18436,14 @@ join_init_quick_read_record(JOIN_TAB *ta
#ifdef OPTIMIZER_TRACE
Opt_trace_context * const trace= tab->join->thd->opt_trace;
- const bool trace_enabled= trace ?
+ const bool repeated_trace_enabled= trace ?
trace->feature_enabled(Opt_trace_context::DYNAMIC_RANGE) :
false;
- Opt_trace_disable_I_S disable_trace_wrapper(trace, !trace_enabled);
+ const bool disable_trace=
+ (tab->select->traced_before && !repeated_trace_enabled);
+ Opt_trace_disable_I_S disable_trace_wrapper(trace, disable_trace);
+
+ tab->select->traced_before= true;
Opt_trace_object wrapper(trace);
Opt_trace_object trace_table(trace, "records_estimation_per_record");
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110114155348-m2513pgvsj6d479q.bundle
