From: Jorgen Loland Date: April 6 2011 9:19am Subject: bzr push into mysql-trunk branch (jorgen.loland:3287 to 3288) List-Archive: http://lists.mysql.com/commits/134786 Message-Id: <20110406091938.CC0FD79F@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3288 Jorgen Loland 2011-04-06 Fix optimizer tracing in range access plan analysis: Fixed misunderstanding: SEL_ARG::next_key_part does not point to a single SEL_ARG but to the root of a SEL_ARG tree * Old optimizer tracing code that effectively printed all intervals of the first keypart and appended it with only the root of consecutive keyparts is replaced with recursive function that combines a range over the current keypart with all ranges in consecutive keyparts * Fixed documentation that describes how SEL_ARG red-black trees are structured. * Added test case that used to fail to print all ranges for a range access plan. Bonus: With new understanding of how SEL_ARG R-B trees are structured, it was possible to: * Remove tracing from sel_arg_range_seq_next() (and all RANGE_SEQ_IF::next() implementations) and step_down_to(). This was replaced with tracing of SEL_ARG trees in the range optimizer. 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/handler.cc sql/handler.h sql/opt_range.cc sql/opt_range.h sql/sql_join_cache.cc 3287 Guilhem Bichot 2011-04-05 removing compiler warning added by me modified: mysys/my_malloc.c === modified file 'mysql-test/include/optimizer_trace_range.inc' --- a/mysql-test/include/optimizer_trace_range.inc 2011-03-21 09:01:05 +0000 +++ b/mysql-test/include/optimizer_trace_range.inc 2011-04-06 09:19:23 +0000 @@ -129,6 +129,13 @@ EXPLAIN SELECT * FROM t2 WHERE (key1a = --echo SELECT * FROM information_schema.OPTIMIZER_TRACE; +# Multiple ranges on key parts in same index +--echo +EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and + (key1a = 4 or key1a = 5); +--echo +SELECT * FROM information_schema.OPTIMIZER_TRACE; + # more_expensive_than_table_scan --echo EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2); === modified file 'mysql-test/r/optimizer_trace_range_no_prot.result' --- a/mysql-test/r/optimizer_trace_range_no_prot.result 2011-03-21 20:50:40 +0000 +++ b/mysql-test/r/optimizer_trace_range_no_prot.result 2011-04-06 09:19:23 +0000 @@ -2340,6 +2340,205 @@ EXPLAIN SELECT * FROM t2 WHERE (key1a = ] /* steps */ } 0 +EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and +(key1a = 4 or key1a = 5); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,i1b i1b 4 NULL 2 Using index condition + +SELECT * FROM information_schema.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE +EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and +(key1a = 4 or key1a = 5) { + "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`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))" + } + ] /* steps */ + } /* join_preparation */ + }, + { + "join_optimization": { + "select#": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `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 7 < key1b < 10", + "5 <= key1a <= 5 AND 7 < key1b < 10" + ] /* ranges */, + "index_only": false, + "records": 2, + "cost": 4.41, + "rowid_ordered": false, + "chosen": true + }, + { + "index": "i1b", + "ranges": [ + "7 < key1b < 10 AND 4 <= key1a <= 4", + "7 < key1b < 10 AND 5 <= key1a <= 5" + ] /* ranges */, + "index_only": false, + "records": 2, + "cost": 3.41, + "rowid_ordered": false, + "chosen": true + } + ] /* 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": "i1b", + "records": 2, + "ranges": [ + "7 < key1b < 10 AND 4 <= key1a <= 4", + "7 < key1b < 10 AND 5 <= key1a <= 5" + ] /* ranges */ + } /* range_access_plan */, + "records_for_plan": 2, + "cost_for_plan": 3.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": 3.41, + "chosen": true + } + ] /* considered_access_paths */ + } /* best_access_path */, + "cost_for_plan": 3.81, + "records_for_plan": 2, + "chosen": true + } + ] /* considered_execution_plans */ + }, + { + "attaching_conditions_to_tables": { + "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))", + "attached_conditions_computation": [ + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "test", + "table": "t2", + "attached": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))" + } + ] /* 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 'mysql-test/r/optimizer_trace_range_ps_prot.result' --- a/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-03-21 20:50:40 +0000 +++ b/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-04-06 09:19:23 +0000 @@ -2340,6 +2340,205 @@ EXPLAIN SELECT * FROM t2 WHERE (key1a = ] /* steps */ } 0 +EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and +(key1a = 4 or key1a = 5); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,i1b i1b 4 NULL 2 Using index condition + +SELECT * FROM information_schema.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE +EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and +(key1a = 4 or key1a = 5) { + "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`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))" + } + ] /* steps */ + } /* join_preparation */ + }, + { + "join_optimization": { + "select#": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `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 7 < key1b < 10", + "5 <= key1a <= 5 AND 7 < key1b < 10" + ] /* ranges */, + "index_only": false, + "records": 2, + "cost": 4.41, + "rowid_ordered": false, + "chosen": true + }, + { + "index": "i1b", + "ranges": [ + "7 < key1b < 10 AND 4 <= key1a <= 4", + "7 < key1b < 10 AND 5 <= key1a <= 5" + ] /* ranges */, + "index_only": false, + "records": 2, + "cost": 3.41, + "rowid_ordered": false, + "chosen": true + } + ] /* 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": "i1b", + "records": 2, + "ranges": [ + "7 < key1b < 10 AND 4 <= key1a <= 4", + "7 < key1b < 10 AND 5 <= key1a <= 5" + ] /* ranges */ + } /* range_access_plan */, + "records_for_plan": 2, + "cost_for_plan": 3.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": 3.41, + "chosen": true + } + ] /* considered_access_paths */ + } /* best_access_path */, + "cost_for_plan": 3.81, + "records_for_plan": 2, + "chosen": true + } + ] /* considered_execution_plans */ + }, + { + "attaching_conditions_to_tables": { + "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))", + "attached_conditions_computation": [ + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "test", + "table": "t2", + "attached": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))" + } + ] /* 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/handler.cc' --- a/sql/handler.cc 2011-03-21 17:55:41 +0000 +++ b/sql/handler.cc 2011-04-06 09:19:23 +0000 @@ -4514,8 +4514,7 @@ handler::multi_range_read_info_const(uin *bufsz= 0; seq_it= seq->init(seq_init_param, n_ranges, *flags); - Opt_trace_array ota(thd->opt_trace, "ranges"); - while (!seq->next(seq_it, &range, &ota)) + while (!seq->next(seq_it, &range)) { if (unlikely(thd->killed != 0)) return HA_POS_ERROR; @@ -4715,7 +4714,7 @@ int handler::multi_range_read_next(char start: /* Try the next range(s) until one matches a record. */ - while (!(range_res= mrr_funcs.next(mrr_iter, &mrr_cur_range, NULL))) + while (!(range_res= mrr_funcs.next(mrr_iter, &mrr_cur_range))) { scan_it_again: result= read_range_first(mrr_cur_range.start_key.keypart_map ? === modified file 'sql/handler.h' --- a/sql/handler.h 2011-03-22 10:08:27 +0000 +++ b/sql/handler.h 2011-04-06 09:19:23 +0000 @@ -1153,15 +1153,12 @@ typedef struct st_range_seq_if next() seq The value returned by RANGE_SEQ_IF::init() range OUT Information about the next range - trace_array Optimizer trace-related information is appended to this if - tracing is enabled. If NULL, no optimizer tracing is done RETURN 0 - Ok, the range structure filled with info about the next range 1 - No more ranges */ - uint (*next) (range_seq_t seq, KEY_MULTI_RANGE *range, - Opt_trace_array *trace_array); + uint (*next) (range_seq_t seq, KEY_MULTI_RANGE *range); /* Check whether range_info orders to skip the next record === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2011-04-05 07:34:13 +0000 +++ b/sql/opt_range.cc 2011-04-06 09:19:23 +0000 @@ -235,15 +235,28 @@ class RANGE_OPT_PARAM; In red-black tree form: - +------------------------------+ - | kp1=2 AND (kp3=11 OR kp3=14) | - +------------------------------+ - / \ - / \ - +------------------------+ +--------------------+ - | kp1 < 1 AND kp2=5 AND | | kp1=3 AND | - | (kp3=10 OR kp3=12) | | (kp3=11 OR kp3=14) | - +------------------------+ +--------------------+ + +-------+ +--------+ + | kp1=2 |.................| kp3=14 | + +-------+ +--------+ + / \ / + +---------+ +-------+ +--------+ + | kp1 < 1 | | kp1=3 | | kp3=11 | + +---------+ +-------+ +--------+ + . . + ...... ....... + . . + +-------+ +--------+ + | kp2=5 | | kp3=14 | + +-------+ +--------+ + . / + . +--------+ + (root of R-B tree | kp3=11 | + for "kp3={10|12}") +--------+ + + + Where / and \ denote left and right pointers and ... denotes + next_key_part pointers to the root of the R-B tree of intervals for + consecutive key parts. 3. SEL_ARG GRAPH USE @@ -372,6 +385,10 @@ public: SEL_ARG *left,*right; /* R-B tree children */ SEL_ARG *next,*prev; /* Links for bi-directional interval list */ SEL_ARG *parent; /* R-B tree parent */ + /* + R-B tree root of intervals covering keyparts consecutive to this + SEL_ARG. See documentation of SEL_ARG GRAPH semantics for details. + */ SEL_ARG *next_key_part; enum leaf_color { BLACK,RED } color; enum Type { IMPOSSIBLE, MAYBE, MAYBE_KEY, KEY_RANGE } type; @@ -843,6 +860,10 @@ static void print_quick(QUICK_SELECT_I * #endif #ifdef OPTIMIZER_TRACE +static void trace_range_all_keyparts(Opt_trace_array &trace_range, + const String *range_so_far, + SEL_ARG *keypart_root, + const KEY_PART_INFO *key_parts); static void append_range(String *out, const KEY_PART_INFO *key_parts, const uchar *min_key, const uchar *max_key, @@ -2063,27 +2084,13 @@ void TRP_RANGE::trace_basic_info(const P Opt_trace_array trace_range(param->thd->opt_trace, "ranges"); - const SEL_ARG *current_range= key; - // navigate from root to first interval in the interval tree - while (current_range->left && current_range->left != &null_element) - current_range= current_range->left; + // TRP_RANGE should not be created if there are no range intervals + DBUG_ASSERT(key); + + String range_info; + range_info.set_charset(system_charset_info); + trace_range_all_keyparts(trace_range, &range_info, key, key_part); - while (current_range) - { - String range_info; - range_info.set_charset(system_charset_info); - for (const SEL_ARG *part= current_range; - part; - part= part->next_key_part) - { - const KEY_PART_INFO *cur_key_part= key_part + part->part; - append_range(&range_info, cur_key_part, - part->min_value, part->max_value, - part->min_flag | part->max_flag); - } - trace_range.add_utf8(range_info.ptr(), range_info.length()); - current_range= current_range->next; - } #endif } @@ -2345,28 +2352,12 @@ void TRP_GROUP_MIN_MAX::trace_basic_info } Opt_trace_array trace_range(param->thd->opt_trace, "ranges"); - - const SEL_ARG *current_range= index_tree; - // navigate from root to first interval in the interval tree - while (current_range && // can have group quick without ranges - current_range->left && current_range->left != &null_element) - current_range= current_range->left; - - while (current_range) + // can have group quick without ranges + if (index_tree) { String range_info; range_info.set_charset(system_charset_info); - for (const SEL_ARG *part= current_range; - part; - part= part->next_key_part) - { - const KEY_PART_INFO *cur_key_part= key_part + part->part; - append_range(&range_info, cur_key_part, - part->min_value, part->max_value, - part->min_flag | part->max_flag); - } - trace_range.add_utf8(range_info.ptr(), range_info.length()); - current_range= current_range->next; + trace_range_all_keyparts(trace_range, &range_info, index_tree, key_part); } #endif } @@ -5391,6 +5382,23 @@ static TRP_RANGE *get_key_scans_params(P found_records= check_quick_select(param, idx, read_index_only, *key, update_tbl_stats, &mrr_flags, &buf_size, &cost); + +#ifdef OPTIMIZER_TRACE + // check_quick_select() says don't use range if it returns HA_POS_ERROR + if (found_records != HA_POS_ERROR && + param->thd->opt_trace && param->thd->opt_trace->is_started()) + { + Opt_trace_array trace_range(param->thd->opt_trace, "ranges"); + + const KEY &cur_key= param->table->key_info[keynr]; + const KEY_PART_INFO *key_part= cur_key.key_part; + + String range_info; + range_info.set_charset(system_charset_info); + trace_range_all_keyparts(trace_range, &range_info, *key, key_part); + } +#endif + trace_idx.add("index_only", read_index_only). add("records", found_records). add("cost", cost.total_cost()); @@ -8038,27 +8046,9 @@ range_seq_t sel_arg_range_seq_init(void condition to this String if not NULL. Used by optimizer trace */ -static void step_down_to(String *trace_string, - SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree) +static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree) { -#ifdef OPTIMIZER_TRACE - if (trace_string != NULL) - { - /* - Stepping down will append the range for the current keypart (in - key_tree) to seq. Trace range here since this is where it is - human readable. - */ - const KEY_PART_INFO *key_part= - arg->param->table->key_info[arg->real_keyno].key_part + key_tree->part; - - append_range(trace_string, key_part, - key_tree->min_value, key_tree->max_value, - key_tree->min_flag | key_tree->max_flag); - } -#endif - RANGE_SEQ_ENTRY *cur= &arg->stack[arg->i+1]; RANGE_SEQ_ENTRY *prev= &arg->stack[arg->i]; @@ -8115,24 +8105,10 @@ static void step_down_to(String *trace_s */ //psergey-merge-todo: support check_quick_keys:max_keypart -uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, - Opt_trace_array *trace_array) +uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { SEL_ARG *key_tree; SEL_ARG_RANGE_SEQ *seq= (SEL_ARG_RANGE_SEQ*)rseq; - String key_range_trace; - //points to key_range_trace if doing tracing, NULL otherwise - String *active_trace_ptr= NULL; - -#ifdef OPTIMIZER_TRACE - if (unlikely(trace_array != NULL) && - unlikely(seq->param->thd->opt_trace != NULL) && - seq->param->thd->opt_trace->is_started()) - { - key_range_trace.set_charset(system_charset_info); - active_trace_ptr= &key_range_trace; - } -#endif if (seq->at_start) { @@ -8150,7 +8126,7 @@ uint sel_arg_range_seq_next(range_seq_t DBUG_ASSERT(key_tree->next != &null_element); //step down; (update the tuple, we'll step right and stay there) seq->i--; - step_down_to(active_trace_ptr, seq, key_tree->next); + step_down_to(seq, key_tree->next); key_tree= key_tree->next; seq->param->is_ror_scan= FALSE; goto walk_right_n_up; @@ -8172,7 +8148,7 @@ uint sel_arg_range_seq_next(range_seq_t DBUG_ASSERT(key_tree->next != &null_element); // Step down; update the tuple seq->i--; - step_down_to(active_trace_ptr, seq, key_tree->next); + step_down_to(seq, key_tree->next); key_tree= key_tree->next; seq->param->is_ror_scan= FALSE; break; @@ -8211,20 +8187,6 @@ walk_right_n_up: &cur->max_key, &cur->max_key_flag, MAX_KEY); - -#ifdef OPTIMIZER_TRACE - if (unlikely(active_trace_ptr != NULL)) - { - // Trace the range we just stored - KEY_PART_INFO *kpi= - seq->param->table->key_info[seq->real_keyno].key_part + - store_key_part->part; - - append_range(active_trace_ptr, kpi, - store_key_part->min_value, store_key_part->max_value, - store_key_part->min_flag | store_key_part->max_flag); - } -#endif break; } } @@ -8242,7 +8204,7 @@ walk_up_n_right: /* Step up */ key_tree= key_tree->prev; } - step_down_to(active_trace_ptr, seq, key_tree); + step_down_to(seq, key_tree); } /* Ok got a tuple */ @@ -8304,9 +8266,6 @@ walk_up_n_right: seq->param->range_count++; seq->param->max_key_part=max(seq->param->max_key_part,key_tree->part); - if (unlikely(active_trace_ptr != NULL) && active_trace_ptr->length()) - trace_array->add_utf8(active_trace_ptr->ptr(), active_trace_ptr->length()); - return 0; } @@ -9329,8 +9288,7 @@ range_seq_t quick_range_seq_init(void *i 1 No more ranges in the sequence */ -uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, - Opt_trace_array *unused) +uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { QUICK_RANGE_SEQ_CTX *ctx= (QUICK_RANGE_SEQ_CTX*)rseq; @@ -10586,6 +10544,21 @@ get_best_group_min_max(PARAM *param, SEL cur_index_tree, TRUE, &mrr_flags, &mrr_bufsize, &dummy_cost); + +#ifdef OPTIMIZER_TRACE + if (cur_index_tree && + param->thd->opt_trace && param->thd->opt_trace->is_started()) + { + Opt_trace_array trace_range(param->thd->opt_trace, "ranges"); + + const KEY_PART_INFO *key_part= cur_index_info->key_part; + + String range_info; + range_info.set_charset(system_charset_info); + trace_range_all_keyparts(trace_range, &range_info, + cur_index_tree, key_part); + } +#endif } cost_group_min_max(table, cur_index_info, cur_used_key_parts, cur_group_key_parts, tree, cur_index_tree, @@ -12433,6 +12406,60 @@ static void append_range(String *out, } } +/** + Traverse an R-B tree of range conditions and append all ranges for this + keypart and consecutive keyparts to the optimizer trace. See + description of R-B trees/SEL_ARG for details on how ranges are + linked. + + @param[in,out] trace_range Optimizer trace array ranges are appended to + @param[in] range_so_far String containing ranges for keyparts prior + to this keypart. + @param[in] keypart_root The root of the R-B tree containing intervals + for this keypart. + @param[in] key_parts Index components description, used when adding + information to the optimizer trace +*/ +static void trace_range_all_keyparts(Opt_trace_array &trace_range, + const String *range_so_far, + SEL_ARG *keypart_root, + const KEY_PART_INFO *key_parts) +{ + DBUG_ASSERT(keypart_root && keypart_root != &null_element); + + // Navigate to first interval in red-black tree + const KEY_PART_INFO *cur_key_part= key_parts + keypart_root->part; + SEL_ARG *keypart_range= keypart_root->first(); + + while (keypart_range) + { + String range_cur_keypart= String(*range_so_far); + + // Append the current range to the range String + append_range(&range_cur_keypart, cur_key_part, + keypart_range->min_value, keypart_range->max_value, + keypart_range->min_flag | keypart_range->max_flag); + + if (keypart_range->next_key_part) + { + // Not done - there are ranges in consecutive keyparts as well + trace_range_all_keyparts(trace_range, &range_cur_keypart, + keypart_range->next_key_part, key_parts); + } + else + { + /* + This is the last keypart with a range. Print full range + info to the optimizer trace + */ + trace_range.add_utf8(range_cur_keypart.ptr(), + range_cur_keypart.length()); + } + keypart_range= keypart_range->next; + } + +} + #endif //OPTIMIZER_TRACE /***************************************************************************** === modified file 'sql/opt_range.h' --- a/sql/opt_range.h 2011-03-17 12:03:30 +0000 +++ b/sql/opt_range.h 2011-04-06 09:19:23 +0000 @@ -394,8 +394,7 @@ typedef struct st_quick_range_seq_ctx } QUICK_RANGE_SEQ_CTX; range_seq_t quick_range_seq_init(void *init_param, uint n_ranges, uint flags); -uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, - Opt_trace_array *unused); +uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range); /* === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2011-03-17 12:03:30 +0000 +++ b/sql/sql_join_cache.cc 2011-04-06 09:19:23 +0000 @@ -2169,8 +2169,7 @@ range_seq_t bka_range_seq_init(void *ini */ static -uint bka_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, - Opt_trace_array *unused) +uint bka_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { DBUG_ENTER("bka_range_seq_next"); JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq; @@ -2994,8 +2993,7 @@ range_seq_t bka_unique_range_seq_init(vo */ static -uint bka_unique_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, - Opt_trace_array *unused) +uint bka_unique_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) { DBUG_ENTER("bka_unique_range_seq_next"); JOIN_CACHE_BKA_UNIQUE *cache= (JOIN_CACHE_BKA_UNIQUE *) rseq; No bundle (reason: useless for push emails).