Jorgen Loland a écrit, Le 15.03.2011 14:31:
> #At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800/
> based on revid:guilhem.bichot@stripped
>
> 3279 Jorgen Loland 2011-03-15
> Added test case for multiple ranges over a multi-keypart range
>
> Trace failed to print all but the final range in the
> chosen_range_access_summary{} section of the optimizer trace.
> Fixed by navigating to the first range interval before printing
> ranges.
>
> modified:
> mysql-test/include/optimizer_trace_range.inc
> mysql-test/r/optimizer_trace_range_no_prot.result
> mysql-test/r/optimizer_trace_range_ps_prot.result
> sql/opt_range.cc
I applied the patch and then applied opt_range.cc, to see what the
problem was:
> === modified file 'mysql-test/r/optimizer_trace_range_no_prot.result'
> --- a/mysql-test/r/optimizer_trace_range_no_prot.result 2011-03-01 10:55:27 +0000
> +++ b/mysql-test/r/optimizer_trace_range_no_prot.result 2011-03-15 13:31:03 +0000
> @@ -2111,6 +2111,207 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
> ] /* steps */
> } 0
>
> +EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
> +(key1a = 4 and key1b < 7 and key1b > 3);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t2 range PRIMARY,i1b PRIMARY 8 NULL 2 Using index condition
> +
> +SELECT * FROM information_schema.OPTIMIZER_TRACE;
> +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE
> +EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
> +(key1a = 4 and key1b < 7 and key1b > 3) {
> + "steps": [
> + {
> + "join_preparation": {
> + "select#": 1,
> + "steps": [
> + {
> + "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS
> `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1`
> AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2`
> where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` <
> 7) and (`test`.`t2`.`key1b` > 3)))"
> + }
> + ] /* steps */
> + } /* join_preparation */
> + },
> + {
> + "join_optimization": {
> + "select#": 1,
> + "steps": [
> + {
> + "condition_processing": {
> + "condition": "WHERE",
> + "original_condition": "(((`test`.`t2`.`key1a` = 5) and
> (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` =
> 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
> + "steps": [
> + {
> + "transformation": "equality_propagation",
> + "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or
> ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4,
> `test`.`t2`.`key1a`)))"
> + },
> + {
> + "transformation": "constant_propagation",
> + "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or
> ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4,
> `test`.`t2`.`key1a`)))"
> + },
> + {
> + "transformation": "trivial_condition_removal",
> + "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and
> (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or
> ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4,
> `test`.`t2`.`key1a`)))"
> + }
> + ] /* steps */
> + } /* condition_processing */
> + },
> + {
> + "ref_optimizer_key_uses": [
> + ] /* ref_optimizer_key_uses */
> + },
> + {
> + "records_estimation": [
> + {
> + "database": "test",
> + "table": "t2",
> + "range_analysis": {
> + "table_scan": {
> + "records": 1024,
> + "cost": 215.15
> + } /* table_scan */,
> + "potential_range_indices": [
> + {
> + "index": "PRIMARY",
> + "usable": true,
> + "key_parts": [
> + "key1a",
> + "key1b"
> + ] /* key_parts */
> + },
> + {
> + "index": "i1b",
> + "usable": true,
> + "key_parts": [
> + "key1b",
> + "key1a"
> + ] /* key_parts */
> + },
> + {
> + "index": "i2_1",
> + "usable": false,
> + "cause": "not_applicable"
> + },
> + {
> + "index": "i2_2",
> + "usable": false,
> + "cause": "not_applicable"
> + }
> + ] /* potential_range_indices */,
> + "setup_range_conditions": [
> + ] /* setup_range_conditions */,
> + "group_index_range": {
> + "chosen": false,
> + "cause": "not_group_by_or_distinct"
> + } /* group_index_range */,
> + "analyzing_range_alternatives": {
> + "range_scan_alternatives": [
> + {
> + "index": "PRIMARY",
> + "ranges": [
> + "4 <= key1a <= 4 AND 3 < key1b < 7",
> + "5 <= key1a <= 5 AND 2 < key1b < 10"
those two ranges are printed
> + ] /* ranges */,
> + "index_only": false,
> + "records": 2,
> + "cost": 4.41,
> + "rowid_ordered": false,
> + "chosen": true
> + },
> + {
> + "index": "i1b",
> + "ranges": [
> + "2 < key1b <= 3 AND 5 <= key1a <= 5",
> + "3 < key1b < 7",
> + "7 <= key1b < 10 AND 5 <= key1a <= 5"
those three too
> + ] /* ranges */,
> + "index_only": false,
> + "records": 6,
> + "cost": 10.21,
> + "rowid_ordered": false,
> + "chosen": false,
> + "cause": "cost"
> + }
> + ] /* range_scan_alternatives */,
> + "analyzing_roworder_intersect": {
> + "usable": false,
> + "cause": "too_few_roworder_scans"
> + } /* analyzing_roworder_intersect */
> + } /* analyzing_range_alternatives */,
> + "chosen_range_access_summary": {
> + "range_access_plan": {
> + "type": "range_scan",
> + "index": "PRIMARY",
> + "records": 2,
> + "ranges": [
> + "4 <= key1a <= 4 AND 3 < key1b < 7",
this one above is not printed (as indicated in the commit comment).
In sel_arg_range_seq_next(), we seem to navigate:
walk_up_n_right:
while (key_tree->prev && key_tree->prev != &null_element)
{
don't you need such "&& key_tree->prev != &null_element" in your
navigation too?
> + "5 <= key1a <= 5 AND 2 < key1b < 10"
> + ] /* ranges */
> + } /* range_access_plan */,
> + "records_for_plan": 2,
> + "cost_for_plan": 4.41,
> + "chosen": true
> + } /* chosen_range_access_summary */
> + } /* range_analysis */
> + }
> + ] /* records_estimation */
> + },
> + {
> + "considered_execution_plans": [
> + {
> + "database": "test",
> + "table": "t2",
> + "best_access_path": {
> + "considered_access_paths": [
> + {
> + "access_type": "range",
> + "records": 2,
> + "cost": 4.41,
> + "chosen": true
> + }
> + ] /* considered_access_paths */
> + } /* best_access_path */,
> + "cost_for_plan": 4.41,
> + "records_for_plan": 2,
> + "chosen": true
> + }
> + ] /* considered_execution_plans */
> + },
> + {
> + "attaching_conditions_to_tables": {
> + "original_condition": "(((`test`.`t2`.`key1a` = 5) and
> (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` =
> 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
> + "attached_conditions_computation": [
> + ] /* attached_conditions_computation */,
> + "attached_conditions_summary": [
> + {
> + "database": "test",
> + "table": "t2",
> + "attached": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b`
> < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and
> (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))"
> + }
> + ] /* attached_conditions_summary */
> + } /* attaching_conditions_to_tables */
> + },
> + {
> + "refine_plan": [
> + {
> + "database": "test",
> + "table": "t2",
> + "scan_type": "table"
> + }
> + ] /* refine_plan */
> + }
> + ] /* steps */
> + } /* join_optimization */
> + },
> + {
> + "join_execution": {
> + "select#": 1,
> + "steps": [
> + ] /* steps */
> + } /* join_execution */
> + }
> + ] /* steps */
> +} 0
> +
> EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 SIMPLE t1 ALL i1,i2 NULL NULL NULL 1024 Using where
>
> === modified file 'sql/opt_range.cc'
> --- a/sql/opt_range.cc 2011-02-16 15:06:06 +0000
> +++ b/sql/opt_range.cc 2011-03-15 13:31:03 +0000
> @@ -2018,13 +2018,17 @@ void TRP_RANGE::trace_basic_info(const P
> add_utf8("index", cur_key.name).add("records", records);
>
> Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
> - for (const SEL_ARG *current= key;
> - current;
> - current= current->next)
> +
> + const SEL_ARG *current_range= key;
do you understand why TRP_RANGE::key is the _last_ of ranges?
In the testcase (added to the .inc file), TRP_RANGE::key is set here
(opt_range.cc:5367):
if (key_to_read)
{
idx= key_to_read - tree->keys;
if ((read_plan= new (param->mem_root) TRP_RANGE(*key_to_read, idx,
best_mrr_flags)))
(the constructor sets TRP_RANGE::key to *key_to_read).
Is there, in the existing code, some place where we take this
TRP_RANGE::key and go up following the "prev" pointers?
Without an existing example, I'm unsure of what we're doing... I'm
unsure of what the _execution_ really searches for in the table:
both
"4 <= key1a <= 4 AND 3 < key1b < 7",
"5 <= key1a <= 5 AND 2 < key1b < 10"
or just
"5 <= key1a <= 5 AND 2 < key1b < 10"
??
> + // make current_range point to the first interval
> + while (current_range->prev)
is it 100% sure that current_range starts non-NULL (so that we can read
its "prev" above)?
> + current_range= current_range->prev;
> +
> + while (current_range)
in other existing code I find:
/* Step down if we can */
if (key_tree->next && key_tree->next != &null_element)
{
// Step down; update the tuple
so don't we need "&& current_range != &null_element" in the above
while() condition?
> {
> String range_info;
> range_info.set_charset(system_charset_info);
> - for (const SEL_ARG *part= current;
> + for (const SEL_ARG *part= current_range;
> part;
> part= part->next_key_part)
> {
> @@ -2034,6 +2038,7 @@ void TRP_RANGE::trace_basic_info(const P
> part->min_flag | part->max_flag);
> }
> trace_range.add_utf8(range_info.ptr(), range_info.length());
> + current_range= current_range->next;
> }
> #endif
> }
