diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 98 |
1 files changed, 38 insertions, 60 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f313d571b5b..6a441c5047b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7887,9 +7887,7 @@ best_access_path(JOIN *join, } /* not ft_key */ if (records < DBL_MAX && - (found_part & 1) && // start_key->key can be used for index access - (s->table->file->index_flags(start_key->key,0,1) & - HA_DO_RANGE_FILTER_PUSHDOWN)) + (found_part & 1)) // start_key->key can be used for index access { double rows= record_count * records; @@ -7914,50 +7912,33 @@ best_access_path(JOIN *join, cost_of_fetching_1_row = tmp/rows cost_of_fetching_1_key_tuple = keyread_tmp/rows - access_cost_factor is the gain we expect for using rowid filter. - An access_cost_factor of 1.0 means that keyread_tmp is 0 - (using key read is infinitely fast) and the gain for each row when - using filter is great. - An access_cost_factor if 0.0 means that using keyread has the - same cost as reading rows, so there is no gain to get with - filter. - access_cost_factor should never be bigger than 1.0 (if all - calculations are correct) as the cost of keyread should always be - smaller than the cost of fetching the same number of keys + rows. - access_cost_factor should also never be smaller than 0.0. - The one exception is if number of records is 1 (eq_ref), then - because we are comparing rows to cost of keyread_tmp, keyread_tmp - is higher by 1.0. This is a big that will be fixed in a later - version. - - If we have limited the cost (=tmp) of reading rows with 'worst_seek' - we cannot use filters as the cost calculation below would cause - tmp to become negative. The future resultion is to not limit - cost with worst_seek. - - We cannot use filter with JT_EQ_REF as in this case 'tmp' is - number of rows from prev_record_read() and keyread_tmp is 0. These - numbers are not usable with rowid filter code. + Currently the calculation of cost_of_fetching_1_row and + cost_of_fetching_1_key_tuple is done by functions that are + not quite correspond each other. Currently there are several + irregularities in the code. For example if we use values + of records_per_key from statistical tables and some engine + uses its own records_per_key to calculate the cost of fetching + records for one key then the value of keyread_tmp might happen + to be bigger than the value of tmp. We avoid this by capping + the value of keyread_tmp by the value tmp in the formula that + calculates possible gain of using a certain rowid filter. + We also have a special handling of the case when the joined + table is accessed by eq_ref access. */ - double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0); - if (!(records < s->worst_seeks && - records <= thd->variables.max_seeks_for_key)) - trace_access_idx.add("rowid_filter_skipped", "worst/max seeks clipping"); - else if (access_cost_factor <= 0.0) - trace_access_idx.add("rowid_filter_skipped", "cost_factor <= 0"); - else if (type != JT_EQ_REF) + + double key_access_cost= + type == JT_EQ_REF ? 0.5 * tmp : MY_MIN(tmp, keyread_tmp); + double access_cost_factor= MY_MIN((tmp - key_access_cost) / rows, 1.0); + filter= + table->best_range_rowid_filter_for_partial_join(start_key->key, + rows, + access_cost_factor); + if (filter) { - filter= - table->best_range_rowid_filter_for_partial_join(start_key->key, - rows, - access_cost_factor); - if (filter) - { - tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); - DBUG_ASSERT(tmp >= 0); - trace_access_idx.add("rowid_filter_key", - s->table->key_info[filter->key_no].name); - } + tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); + DBUG_ASSERT(tmp >= 0); + trace_access_idx.add("rowid_filter_key", + s->table->key_info[filter->key_no].name); } } trace_access_idx.add("rows", records).add("cost", tmp); @@ -8110,23 +8091,20 @@ best_access_path(JOIN *join, uint key_no= s->quick->index; /* See the comment concerning using rowid filter for with ref access */ - keyread_tmp= s->table->quick_index_only_costs[key_no] * record_count; - double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0); - if (access_cost_factor > 0.0) + double row_access_cost= s->quick->read_time * record_count; + double key_access_cost= + MY_MIN(row_access_cost, + s->table->quick_index_only_costs[key_no] * record_count); + double access_cost_factor= MY_MIN((row_access_cost - key_access_cost) / + rows, 1.0); + filter= + s->table->best_range_rowid_filter_for_partial_join(key_no, rows, + access_cost_factor); + if (filter) { - filter= - s->table-> - best_range_rowid_filter_for_partial_join(key_no, rows, - access_cost_factor); - if (filter) - { - tmp-= filter->get_adjusted_gain(rows); - DBUG_ASSERT(tmp >= 0); - } + tmp-= filter->get_adjusted_gain(rows); + DBUG_ASSERT(tmp >= 0); } - else - trace_access_scan.add("rowid_filter_skipped", "cost_factor <= 0"); - type= JT_RANGE; } else |