Hello,
I'm attaching a result file for discussion.
I got it by using your patch and disabling the assertion in
Opt_trace_context::syntax_error(), so that the test can proceed further.
There is a first problem in "attached_conditions", but easy to solve and
I have just pushed a fix for it (revno 3221). The more interesting
happens at end of object "join_optimization" (look for the "invalid
JSON" words around there).
For such query:
select * from t1, t2
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
we can see that the range optimizer is called once per row of t1: we
read a row of t1, and we call the range optimizer with the range "2 days
before or after this row of t1". This is the so-called "range checked
for each record" (visible in EXPLAIN). Two rows in t1, so two pieces of
trace related to the range optimizer.
In my test there are only two outer rows, but imagine if there would be
1000 outer rows: we would have 1000 traces of the range optimizer.
What is the right approach?
1) Try to disable tracing at the right moment, to avoid this?
2) Or let this tracing be, saying that the range optimizer, even called
1000 times in the execution phase, is still an important optimization
information, and just count on truncation by
@@optimizer_trace_max_mem_size (default: 16kB) if things go unreasonably
big? That doesn't sound too bad to me. If we go this way, I should
define a proper container for those range-opt-at-exec-time traces: a
"join_execution" object in the trace seems mandated, with a "steps"
array underneath; each range optimizer trace becomes an element of the
array. There is another problem: traces generated by execution have the
potential to be big, so truncation by max_mem_size would become
frequent; a truncated trace is not parsable by a JSON parser. So the
only way for a user to get a parsable trace is to increase max_mem_size
until it's big enough to host the execution trace, and it may be
unreasonably big then. So maybe we need a flag in JOIN::exec()'s top
object ("EXECUTION"?), so that the user can request no tracing in
JOIN::exec() and its children. Then the trace stays small, below
max_mem_size, not truncated, and parsable.
Disabling tracing at the start of JOIN::exec() is not a solution: in
other queries (like the first one using a subquery, in
optimizer_trace_no_prot.test), JOIN::optimize for the subquery is called
by the top query's JOIN::exec... so if I disable tracing of
JOIN::exec(), I lose tracing of the subquery's JOIN::optimize().
Do you have an opinion?
--
Mr. Guilhem Bichot <guilhem.bichot@stripped>
Oracle / MySQL / Optimizer team, Lead Software Engineer
Bordeaux, France
www.oracle.com / www.mysql.com
create table t1(a date);
create table t2(a date, key(a));
insert into t1 values('2005-10-01'),('2010-10-12');
insert into t2 values('2005-10-02'),('2010-10-13');
set optimizer_trace="enabled=on,end_marker=on";
select * from t1, t2
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
a a
2005-10-01 2005-10-02
2010-10-12 2010-10-13
SELECT trace FROM information_schema.optimizer_trace;
trace
{
"top_query": "select * from t1, t2\nwhere t2.a between t1.a - interval 2 day and t1.a +
interval 2 day",
"steps": [
{
"expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` join
`test`.`t2` where (`t2`.`a` between (`t1`.`a` - interval 2 day) and (`t1`.`a` + interval
2 day))"
},
{
"join_preparation": {
"select#": 1,
"steps": [
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`test`.`t2`.`a` between (`test`.`t1`.`a` - interval
2 day) and (`test`.`t1`.`a` + interval 2 day))",
"after_equality_propagation": "(`test`.`t2`.`a` between (`test`.`t1`.`a` -
interval 2 day) and (`test`.`t1`.`a` + interval 2 day))",
"after_constant_propagation": "(`test`.`t2`.`a` between (`test`.`t1`.`a` -
interval 2 day) and (`test`.`t1`.`a` + interval 2 day))",
"after_trivial_conditions_removal": "(`test`.`t2`.`a` between
(`test`.`t1`.`a` - interval 2 day) and (`test`.`t1`.`a` + interval 2 day))"
} /* condition_processing */
},
{
"ref-optimizer-key-uses": [
] /* ref-optimizer-key-uses */
},
{
"constant_tables": [
] /* constant_tables */,
"records_estimation": [
{
"table": "t1",
"table_scan": {
"records": 2,
"cost": 2
} /* table_scan */
},
{
"table": "t2",
"table_scan": {
"records": 2,
"cost": 2
} /* table_scan */
}
] /* records_estimation */
},
{
"considered_execution_plans": [
{
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"using_range_access": false,
"using_join_cache": true,
"with_where_cond_processing": {
"records": 2,
"cost": 2.0034
} /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2.0034,
"records_for_plan": 2,
"rest_of_plan": [
{
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"using_range_access": false,
"using_join_cache": true,
"with_where_cond_processing": {
"records": 2,
"cost": 2.0035
} /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 4.007,
"records_for_plan": 4,
"chosen": true
}
] /* rest_of_plan */
},
{
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"using_range_access": false,
"using_join_cache": true,
"with_where_cond_processing": {
"records": 2,
"cost": 2.0034
} /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2.0034,
"records_for_plan": 2,
"pruned_by_heuristic": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`test`.`t2`.`a` between (`test`.`t1`.`a` - interval
2 day) and (`test`.`t1`.`a` + interval 2 day))",
"attached_conditions": [
{
"table": "t1",
"attached": null
},
** invalid JSON (unexpected key "range_analysis") ** {
"table_scan": {
"records": 2,
"cost": 4.5034
} /* table_scan */,
"potential_range_indices": [
{
"index": "a",
"key_parts": [
"a"
] /* key_parts */
}
] /* potential_range_indices */,
"best_full_index_scan": {
"index": "a",
"cost": 1.4213,
"chosen": true
} /* best_full_index_scan */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "a",
"index_only": true,
"records": 18446744073709551615,
"cost": 0,
"rowid_ordered": false,
"chosen": false,
"cause": "higher_cost"
}
] /* range_scan_alternatives */,
"analyzing_ror_intersect": {
"usable": false,
"cause": "too_few_ror_scans"
} /* analyzing_ror_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"chosen": false
} /* chosen_range_access_summary */,
"records": 2
} /* range_analysis */,
{
"table": "t2",
"attached": "(`test`.`t2`.`a` between (`test`.`t1`.`a` - interval 2 day)
and (`test`.`t1`.`a` + interval 2 day))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
}
] /* steps */
} /* join_optimization */
},
** invalid JSON (unexpected key "range_analysis") ** {
"table_scan": {
"records": 2,
"cost": 4.5034
} /* table_scan */,
"potential_range_indices": [
{
"index": "a",
"key_parts": [
"a"
] /* key_parts */
}
] /* potential_range_indices */,
"best_full_index_scan": {
"index": "a",
"cost": 1.4213,
"chosen": true
} /* best_full_index_scan */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "a",
"ranges": [
"2005-09-29 <= a <= 2005-10-03"
] /* ranges */,
"index_only": true,
"records": 1,
"cost": 2.21,
"rowid_ordered": false,
"chosen": false,
"cause": "higher_cost"
}
] /* range_scan_alternatives */,
"analyzing_ror_intersect": {
"usable": false,
"cause": "too_few_ror_scans"
} /* analyzing_ror_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"chosen": false
} /* chosen_range_access_summary */,
"records": 2
} /* range_analysis */,
** invalid JSON (unexpected key "range_analysis") ** {
"table_scan": {
"records": 2,
"cost": 4.5034
} /* table_scan */,
"potential_range_indices": [
{
"index": "a",
"key_parts": [
"a"
] /* key_parts */
}
] /* potential_range_indices */,
"best_full_index_scan": {
"index": "a",
"cost": 1.4213,
"chosen": true
} /* best_full_index_scan */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "a",
"ranges": [
"2010-10-10 <= a <= 2010-10-14"
] /* ranges */,
"index_only": true,
"records": 1,
"cost": 2.21,
"rowid_ordered": false,
"chosen": false,
"cause": "higher_cost"
}
] /* range_scan_alternatives */,
"analyzing_ror_intersect": {
"usable": false,
"cause": "too_few_ror_scans"
} /* analyzing_ror_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"chosen": false
} /* chosen_range_access_summary */,
"records": 2
} /* range_analysis */
] /* steps */
}
