diff options
Diffstat (limited to 'sql/sql_select.cc')
| -rw-r--r-- | sql/sql_select.cc | 1430 |
1 files changed, 855 insertions, 575 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8f68c929a9e..97692463310 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -70,7 +70,7 @@ struct st_sargable_param; static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array); static bool make_join_statistics(JOIN *join, List<TABLE_LIST> &leaves, - COND *conds, DYNAMIC_ARRAY *keyuse); + DYNAMIC_ARRAY *keyuse); static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse, JOIN_TAB *join_tab, uint tables, COND *conds, @@ -352,7 +352,7 @@ bool handle_select(THD *thd, LEX *lex, select_result *result, else { SELECT_LEX_UNIT *unit= &lex->unit; - unit->set_limit(unit->global_parameters); + unit->set_limit(unit->global_parameters()); /* 'options' of mysql_select will be set in JOIN, as far as JOIN for every PS/SP execution new, we will not need reset this flag if @@ -793,7 +793,8 @@ JOIN::prepare(Item ***rref_pointer_array, ref_pointer_array= *rref_pointer_array; /* Resolve the ORDER BY that was skipped, then remove it. */ - if (skip_order_by && select_lex != select_lex->master_unit()->global_parameters) + if (skip_order_by && select_lex != + select_lex->master_unit()->global_parameters()) { if (setup_order(thd, (*rref_pointer_array), tables_list, fields_list, all_fields, select_lex->order_list.first)) @@ -1033,6 +1034,9 @@ int JOIN::optimize() subquery), returns 1 - another JOIN::optimize() call made, and now join->optimize() will return 0, even though we never had a query plan. + + Can have QEP_NOT_PRESENT_YET for degenerate queries (for example, + SELECT * FROM tbl LIMIT 0) */ if (was_optimized != optimized && !res && have_query_plan != QEP_DELETED) { @@ -1336,7 +1340,7 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S /* Calculate how to do the join */ THD_STAGE_INFO(thd, stage_statistics); - if (make_join_statistics(this, select_lex->leaf_tables, conds, &keyuse) || + if (make_join_statistics(this, select_lex->leaf_tables, &keyuse) || thd->is_fatal_error) { DBUG_PRINT("error",("Error: make_join_statistics() failed")); @@ -1873,6 +1877,9 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S } } + if ((select_lex->options & OPTION_SCHEMA_TABLE)) + optimize_schema_tables_reads(this); + tmp_having= having; if (select_options & SELECT_DESCRIBE) { @@ -1919,6 +1926,7 @@ setup_subq_exit: error= 0; derived_exit: + select_lex->mark_const_derived(zero_result_cause); DBUG_RETURN(0); } @@ -2059,6 +2067,7 @@ int JOIN::init_execution() &join_tab[const_tables].table-> keys_in_use_for_order_by)) order=0; + join_tab[const_tables].update_explain_data(const_tables); } } @@ -2355,6 +2364,20 @@ void JOIN::save_explain_data(Explain_query *output, bool can_overwrite, } save_explain_data_intern(thd->lex->explain, need_tmp_table, need_order, distinct, message); + return; + } + + /* + Can have join_tab==NULL for degenerate cases (e.g. SELECT .. UNION ... SELECT LIMIT 0) + */ + if (select_lex == select_lex->master_unit()->fake_select_lex && join_tab) + { + /* + This is fake_select_lex. It has no query plan, but we need to set up a + tracker for ANALYZE + */ + Explain_union *eu= output->get_union(select_lex->master_unit()->first_select()->select_number); + join_tab[0].tracker= eu->get_fake_select_lex_tracker(); } } @@ -2369,15 +2392,6 @@ void JOIN::exec() ); exec_inner(); - if (!exec_saved_explain) - { - save_explain_data(thd->lex->explain, true /* can overwrite */, - need_tmp, - order != 0 && !skip_sort_order, - select_distinct); - exec_saved_explain= true; - } - DBUG_EXECUTE_IF("show_explain_probe_join_exec_end", if (dbug_user_var_equals_int(thd, "show_explain_probe_select_id", @@ -2472,13 +2486,6 @@ void JOIN::exec_inner() thd->set_examined_row_count(0); DBUG_VOID_RETURN; } - /* - Don't reset the found rows count if there're no tables as - FOUND_ROWS() may be called. Never reset the examined row count here. - It must be accumulated from all join iterations of all join parts. - */ - if (table_count) - thd->limit_found_rows= 0; /* Evaluate expensive constant conditions that were not evaluated during @@ -2552,15 +2559,19 @@ void JOIN::exec_inner() simple_order= simple_group; skip_sort_order= 0; } + bool made_call= false; if (order && (order != group_list || !(select_options & SELECT_BIG_RESULT)) && (const_tables == table_count || ((simple_order || skip_sort_order) && - test_if_skip_sort_order(&join_tab[const_tables], order, + (made_call=true) && + test_if_skip_sort_order(&join_tab[const_tables], order, select_limit, 0, &join_tab[const_tables].table-> keys_in_use_for_query)))) order=0; + if (made_call) + join_tab[const_tables].update_explain_data(const_tables); having= tmp_having; select_describe(this, need_tmp, order != 0 && !skip_sort_order, @@ -3078,7 +3089,6 @@ void JOIN::exec_inner() *curr_fields_list), Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF); error= do_select(curr_join, curr_fields_list, NULL, procedure); - thd->limit_found_rows= curr_join->send_records; if (curr_join->order && curr_join->filesort_found_rows) { /* Use info provided by filesort. */ @@ -3245,7 +3255,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array, if (select_lex->linkage != GLOBAL_OPTIONS_TYPE) { //here is EXPLAIN of subselect or derived table - if (join->change_result(result)) + if (join->change_result(result, NULL)) { DBUG_RETURN(TRUE); } @@ -3343,7 +3353,8 @@ static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, select->head=table; table->reginfo.impossible_range=0; if ((error= select->test_quick_select(thd, *(key_map *)keys,(table_map) 0, - limit, 0, FALSE)) == 1) + limit, 0, FALSE, + TRUE /* remove_where_parts*/)) == 1) DBUG_RETURN(select->quick->records); if (error == -1) { @@ -3381,7 +3392,7 @@ typedef struct st_sargable_param static bool make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, - COND *conds, DYNAMIC_ARRAY *keyuse_array) + DYNAMIC_ARRAY *keyuse_array) { int error= 0; TABLE *table; @@ -3585,10 +3596,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, } } - if (conds || outer_join) + if (join->conds || outer_join) { if (update_ref_and_keys(join->thd, keyuse_array, stat, join->table_count, - conds, ~outer_join, join->select_lex, &sargables)) + join->conds, ~outer_join, join->select_lex, &sargables)) goto error; /* Keyparts without prefixes may be useful if this JOIN is a subquery, and @@ -3832,9 +3843,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, } join->impossible_where= false; - if (conds && const_count) - { + if (join->conds && const_count) + { + Item* &conds= join->conds; COND_EQUAL *orig_cond_equal = join->cond_equal; + conds->update_used_tables(); conds= remove_eq_conds(join->thd, conds, &join->cond_value); if (conds && conds->type() == Item::COND_ITEM && @@ -3846,7 +3859,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, join->impossible_where= true; conds=new Item_int((longlong) 0,1); } - join->conds= conds; + join->cond_equal= NULL; if (conds) { @@ -3945,12 +3958,18 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, { select= make_select(s->table, found_const_table_map, found_const_table_map, - *s->on_expr_ref ? *s->on_expr_ref : conds, + *s->on_expr_ref ? *s->on_expr_ref : join->conds, 1, &error); if (!select) goto error; records= get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit); + /* Range analyzer could modify the condition. */ + if (*s->on_expr_ref) + *s->on_expr_ref= select->cond; + else + join->conds= select->cond; + s->quick=select->quick; s->needed_reg=select->needed_reg; select->quick=0; @@ -3961,7 +3980,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (join->thd->variables.optimizer_use_condition_selectivity > 1) calculate_cond_selectivity_for_table(join->thd, s->table, *s->on_expr_ref ? - *s->on_expr_ref : conds); + s->on_expr_ref : &join->conds); if (s->table->reginfo.impossible_range) { impossible_range= TRUE; @@ -9028,6 +9047,20 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table) join_tab->read_first_record= join_init_read_record; join_tab->join= this; join_tab->ref.key_parts= 0; + + uint select_nr= select_lex->select_number; + if (select_nr == INT_MAX) + { + /* this is a fake_select_lex of a union */ + select_nr= select_lex->master_unit()->first_select()->select_number; + join_tab->tracker= thd->lex->explain->get_union(select_nr)-> + get_tmptable_read_tracker(); + } + else + { + join_tab->tracker= thd->lex->explain->get_select(select_nr)-> + get_using_temporary_read_tracker(); + } bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); temp_table->status=0; temp_table->null_row=0; @@ -9593,7 +9626,9 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (tab->table) { tab->table->file->pushed_cond= NULL; - if (thd->use_cond_push(tab->table->file) && !first_inner_tab) + if ((tab->table->file->ha_table_flags() & + HA_CAN_TABLE_CONDITION_PUSHDOWN) && + !first_inner_tab) { COND *push_cond= make_cond_for_table(thd, tmp, current_map, current_map, @@ -9686,7 +9721,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt), 0, - FALSE) < 0) + FALSE, FALSE) < 0) { /* Before reporting "Impossible WHERE" for the whole query @@ -9700,7 +9735,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt),0, - FALSE) < 0) + FALSE, FALSE) < 0) DBUG_RETURN(1); // Impossible WHERE } else @@ -9931,8 +9966,8 @@ uint get_next_field_for_derived_key_simple(uchar *arg) return (uint) (-1); TABLE *table= keyuse->table; uint key= keyuse->key; - uint fldno= keyuse->keypart; - for ( ; + uint fldno= keyuse->keypart; + for ( ; keyuse->table == table && keyuse->key == key && keyuse->keypart == fldno; keyuse++) ; @@ -9974,9 +10009,9 @@ bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys) { KEYUSE *save_first_keyuse= first_keyuse; if (table->check_tmp_key(table->s->keys, parts, - get_next_field_for_derived_key_simple, + get_next_field_for_derived_key_simple, (uchar *) &first_keyuse)) - + { first_keyuse= save_first_keyuse; if (table->add_tmp_key(table->s->keys, parts, @@ -10080,7 +10115,7 @@ bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array) void JOIN::drop_unused_derived_keys() { JOIN_TAB *tab; - for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { @@ -10781,7 +10816,7 @@ void check_join_cache_usage_for_tables(JOIN *join, ulonglong options, JOIN_TAB *tab; JOIN_TAB *prev_tab; - for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -10789,7 +10824,7 @@ void check_join_cache_usage_for_tables(JOIN *join, ulonglong options, } uint idx= join->const_tables; - for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); + for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -10989,7 +11024,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) check_join_cache_usage_for_tables(join, options, no_jbuf_after); JOIN_TAB *first_tab; - for (tab= first_tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); + for (tab= first_tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); tab; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { @@ -11683,7 +11718,7 @@ void JOIN::cleanup(bool full) } if (full) { - JOIN_TAB *sort_tab= first_linear_tab(this, WITH_BUSH_ROOTS, + JOIN_TAB *sort_tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); if (pre_sort_join_tab) { @@ -12162,6 +12197,14 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, if (having && having->val_int() == 0) send_row=0; } + + /* Update results for FOUND_ROWS */ + if (!join->send_row_on_empty_set()) + { + join->thd->set_examined_row_count(0); + join->thd->limit_found_rows= 0; + } + if (!(result->send_result_set_metadata(fields, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))) { @@ -12171,9 +12214,6 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, if (!send_error) result->send_eof(); // Should be safe } - /* Update results for FOUND_ROWS */ - join->thd->limit_found_rows= 0; - join->thd->set_examined_row_count(0); DBUG_RETURN(0); } @@ -12783,8 +12823,8 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, } if (and_level) { - args->concat(&eq_list); - args->concat((List<Item> *)&cond_equal.current_level); + args->append(&eq_list); + args->append((List<Item> *)&cond_equal.current_level); } } else if (cond->type() == Item::FUNC_ITEM || @@ -12839,7 +12879,7 @@ static COND *build_equal_items_for_cond(THD *thd, COND *cond, } and_cond->cond_equal.copy(cond_equal); cond_equal.current_level= and_cond->cond_equal.current_level; - args->concat((List<Item> *)&cond_equal.current_level); + args->append((List<Item> *)&cond_equal.current_level); return and_cond; } @@ -13501,7 +13541,7 @@ static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, This is a fatal error now. However we bail out by returning the original condition that we had before we started the transformation. */ - cond_list->concat((List<Item> *) &cond_equal->current_level); + cond_list->append((List<Item> *) &cond_equal->current_level); } } } @@ -14772,7 +14812,7 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) if (eq_item->const_item() && eq_item->val_int()) it.remove(); } - cond_arg_list->concat((List<Item> *) cond_equalities); + cond_arg_list->append((List<Item> *) cond_equalities); } List<Item_equal> new_equalities; @@ -14829,7 +14869,7 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) of cond_arg_list all together. */ new_item_arg_list->disjoin((List<Item> *) new_item_equalities); - new_equalities.concat(new_item_equalities); + new_equalities.append(new_item_equalities); } } if (new_item_arg_list->is_empty()) @@ -14924,7 +14964,7 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) } } } - cond_arg_list->concat((List<Item> *) cond_equalities); + cond_arg_list->append((List<Item> *) cond_equalities); /* Propagate the newly formed multiple equalities to the all AND/OR levels of cond @@ -15124,6 +15164,11 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) @retval true can be used @retval false cannot be used */ + +/* + psergey-todo: this returns false for int_column='1234' (here '1234' is a + constant. Need to discuss this with Bar). +*/ static bool test_if_equality_guarantees_uniqueness(Item *l, Item *r) { @@ -17377,6 +17422,9 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) if (error == NESTED_LOOP_QUERY_LIMIT) error= NESTED_LOOP_OK; /* select_limit used */ } + + join->thd->limit_found_rows= join->send_records; + if (error == NESTED_LOOP_NO_MORE_ROWS || join->thd->killed == ABORT_QUERY) error= NESTED_LOOP_OK; @@ -17665,6 +17713,8 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) (*join_tab->next_select)(join,join_tab+1,end_of_records); DBUG_RETURN(nls); } + join_tab->tracker->r_scans++; + int error; enum_nested_loop_state rc= NESTED_LOOP_OK; READ_RECORD *info= &join_tab->read_record; @@ -17800,6 +17850,8 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ } + join_tab->tracker->r_rows++; + if (join_tab->table->vfield) update_virtual_fields(join->thd, join_tab->table); @@ -17818,6 +17870,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, There is no select condition or the attached pushed down condition is true => a match is found. */ + join_tab->tracker->r_rows_after_where++; bool found= 1; while (join_tab->first_unmatched && found) { @@ -18579,7 +18632,7 @@ test_if_quick_select(JOIN_TAB *tab) tab->select->quick=0; return tab->select->test_quick_select(tab->join->thd, tab->keys, (table_map) 0, HA_POS_ERROR, 0, - FALSE); + FALSE, /*remove where parts*/FALSE); } @@ -19751,12 +19804,21 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, { KEY_PART_INFO *key_part,*key_part_end; key_part=table->key_info[idx].key_part; - key_part_end=key_part+table->key_info[idx].user_defined_key_parts; + key_part_end=key_part + table->key_info[idx].ext_key_parts; key_part_map const_key_parts=table->const_key_parts[idx]; + uint user_defined_kp= table->key_info[idx].user_defined_key_parts; int reverse=0; uint key_parts; - my_bool on_pk_suffix= FALSE; + bool have_pk_suffix= false; + uint pk= table->s->primary_key; DBUG_ENTER("test_if_order_by_key"); + + if ((table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && + table->key_info[idx].ext_key_part_map && + pk != MAX_KEY && pk != idx) + { + have_pk_suffix= true; + } for (; order ; order=order->next, const_key_parts>>=1) { @@ -19769,58 +19831,37 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, */ for (; const_key_parts & 1 ; const_key_parts>>= 1) key_part++; + + /* + This check was in this function historically (although I think it's + better to check it outside of this function): - if (key_part >= key_part_end) - { - /* - We are at the end of the key. Check if the engine has the primary - key as a suffix to the secondary keys. If it has continue to check - the primary key as a suffix. - */ - if (!on_pk_suffix && (table->key_info[idx].ext_key_part_map & 1) && - (table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && - table->s->primary_key != MAX_KEY && - table->s->primary_key != idx) - { - KEY_PART_INFO *start,*end; - uint pk_part_idx= 0; - on_pk_suffix= TRUE; - start= key_part= table->key_info[table->s->primary_key].key_part; - const_key_parts=table->const_key_parts[table->s->primary_key]; + "Test if the primary key parts were all const (i.e. there's one row). + The sorting doesn't matter" - /* - Calculate true key_part_end and const_key_parts - (we have to stop as first not continous primary key part) - */ - for (key_part_end= key_part, - end= key_part+table->key_info[table->s->primary_key].user_defined_key_parts; - key_part_end < end; key_part_end++, pk_part_idx++) - { - /* Found hole in the pk_parts; Abort */ - if (!(table->key_info[idx].ext_key_part_map & - (((key_part_map) 1) << pk_part_idx))) - break; - } - - /* Adjust const_key_parts */ - const_key_parts&= (((key_part_map) 1) << pk_part_idx) -1; + So, we're checking that + (1) this is an extended key + (2) we've reached its end + */ + key_parts= (key_part - table->key_info[idx].key_part); + if (have_pk_suffix && + reverse == 0 && // all were =const so far + key_parts == table->key_info[idx].ext_key_parts && + table->const_key_parts[pk] == PREV_BITS(uint, + table->key_info[pk]. + user_defined_key_parts)) + { + key_parts= 0; + reverse= 1; // Key is ok to use + goto ok; + } - for (; const_key_parts & 1 ; const_key_parts>>= 1) - key_part++; - /* - Test if the primary key parts were all const (i.e. there's one row). - The sorting doesn't matter. - */ - if (key_part == start+table->key_info[table->s->primary_key].user_defined_key_parts && - reverse == 0) - { - key_parts= 0; - reverse= 1; // Key is ok to use - goto ok; - } - } - else - DBUG_RETURN(0); + if (key_part == key_part_end) + { + /* + There are some items left in ORDER BY that we don't + */ + DBUG_RETURN(0); } if (key_part->field != field || !field->part_of_sortkey.is_set(idx)) @@ -19835,27 +19876,20 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx, if (key_part < key_part_end) key_part++; } - if (on_pk_suffix) - { - uint used_key_parts_secondary= table->key_info[idx].user_defined_key_parts; - uint used_key_parts_pk= - (uint) (key_part - table->key_info[table->s->primary_key].key_part); - key_parts= used_key_parts_pk + used_key_parts_secondary; - if (reverse == -1 && - (!(table->file->index_flags(idx, used_key_parts_secondary - 1, 1) & - HA_READ_PREV) || - !(table->file->index_flags(table->s->primary_key, - used_key_parts_pk - 1, 1) & HA_READ_PREV))) - reverse= 0; // Index can't be used - } - else + key_parts= (uint) (key_part - table->key_info[idx].key_part); + + if (reverse == -1 && + !(table->file->index_flags(idx, user_defined_kp, 1) & HA_READ_PREV)) + reverse= 0; // Index can't be used + + if (have_pk_suffix && reverse == -1) { - key_parts= (uint) (key_part - table->key_info[idx].key_part); - if (reverse == -1 && - !(table->file->index_flags(idx, key_parts-1, 1) & HA_READ_PREV)) + uint pk_parts= table->key_info[pk].user_defined_key_parts; + if (!table->file->index_flags(pk, pk_parts, 1) & HA_READ_PREV) reverse= 0; // Index can't be used } + ok: if (used_key_parts != NULL) *used_key_parts= key_parts; @@ -19943,7 +19977,12 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, uint best= MAX_KEY; KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part; KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts; - + + /* + Find the shortest key that + - produces the required ordering + - has key #ref (up to ref_key_parts) as its subkey. + */ for (nr= 0 ; nr < table->s->keys ; nr++) { if (usable_keys->is_set(nr) && @@ -20136,7 +20175,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, been taken into account. */ usable_keys= *map; - + + /* Find indexes that cover all ORDER/GROUP BY fields */ for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next) { Item *item= (*tmp_order->item)->real_item(); @@ -20156,6 +20196,10 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, { ref_key= tab->ref.key; ref_key_parts= tab->ref.key_parts; + /* + todo: why does JT_REF_OR_NULL mean filesort? We could find another index + that satisfies the ordering. I would just set ref_key=MAX_KEY here... + */ if (tab->type == JT_REF_OR_NULL || tab->type == JT_FT) goto use_filesort; } @@ -20182,15 +20226,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if (ref_key >= 0 && ref_key != MAX_KEY) { - /* - We come here when there is a REF key. - */ + /* Current access method uses index ref_key with ref_key_parts parts */ if (!usable_keys.is_set(ref_key)) { - /* - We come here when ref_key is not among usable_keys - */ + /* However, ref_key doesn't match the needed ordering */ uint new_ref_key; + /* If using index only read, only consider other possible index only keys @@ -20206,27 +20247,23 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts, &usable_keys)) < MAX_KEY) { - if (tab->ref.key >= 0) - { - /* - We'll use ref access method on key new_ref_key. In general case - the index search tuple for new_ref_key will be different (e.g. - when one index is defined as (part1, part2, ...) and another as - (part1, part2(N), ...) and the WHERE clause contains - "part1 = const1 AND part2=const2". - So we build tab->ref from scratch here. - */ - KEYUSE *keyuse= tab->keyuse; - while (keyuse->key != new_ref_key && keyuse->table == tab->table) - keyuse++; - if (create_ref_for_key(tab->join, tab, keyuse, FALSE, - (tab->join->const_table_map | - OUTER_REF_TABLE_BIT))) - goto use_filesort; + /* + Index new_ref_key + - produces the required ordering, + - also has the same columns as ref_key for #ref_key_parts (this + means we will read the same number of rows as with ref_key). + */ - pick_table_access_method(tab); - } - else + /* + If new_ref_key allows to construct a quick select which uses more key + parts than ref(new_ref_key) would, do that. + + Otherwise, construct a ref access (todo: it's not clear what is the + win in using ref access when we could use quick select also?) + */ + if ((table->quick_keys.is_set(new_ref_key) && + table->quick_key_parts[new_ref_key] > ref_key_parts) || + !(tab->ref.key >= 0)) { /* The range optimizer constructed QUICK_RANGE for ref_key, and @@ -20251,19 +20288,47 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, (tab->join->select_options & OPTION_FOUND_ROWS) ? HA_POS_ERROR : - tab->join->unit->select_limit_cnt,0, - TRUE) <= 0; + tab->join->unit->select_limit_cnt,TRUE, + TRUE, FALSE) <= 0; if (res) { select->cond= save_cond; goto use_filesort; } + DBUG_ASSERT(tab->select->quick); + tab->type= JT_ALL; + tab->ref.key= -1; + tab->ref.key_parts= 0; + tab->use_quick= 1; + best_key= new_ref_key; /* We don't restore select->cond as we want to use the original condition as index condition pushdown is not active for the new index. + todo: why not perform index condition pushdown for the new index? */ } + else + { + /* + We'll use ref access method on key new_ref_key. In general case + the index search tuple for new_ref_key will be different (e.g. + when one index is defined as (part1, part2, ...) and another as + (part1, part2(N), ...) and the WHERE clause contains + "part1 = const1 AND part2=const2". + So we build tab->ref from scratch here. + */ + KEYUSE *keyuse= tab->keyuse; + while (keyuse->key != new_ref_key && keyuse->table == tab->table) + keyuse++; + if (create_ref_for_key(tab->join, tab, keyuse, FALSE, + (tab->join->const_table_map | + OUTER_REF_TABLE_BIT))) + goto use_filesort; + + pick_table_access_method(tab); + } + ref_key= new_ref_key; changed_key= true; } @@ -20299,7 +20364,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, !(table->file->index_flags(best_key, 0, 1) & HA_CLUSTERED_INDEX))) goto use_filesort; - if (select && + if (select && // psergey: why doesn't this use a quick? table->quick_keys.is_set(best_key) && best_key != ref_key) { key_map map; @@ -20310,7 +20375,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, join->select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : join->unit->select_limit_cnt, - TRUE, FALSE); + TRUE, FALSE, FALSE); } order_direction= best_key_direction; /* @@ -20369,6 +20434,12 @@ check_reverse_order: */ if (!table->covering_keys.is_set(best_key)) table->disable_keyread(); + else + { + if (!table->key_read) + table->enable_keyread(); + } + if (!quick_created) { if (select) // Throw any existing quick select @@ -20632,7 +20703,12 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, test_if_skip_sort_order(tab,order,select_limit,0, is_order_by ? &table->keys_in_use_for_order_by : &table->keys_in_use_for_group_by)) + { + tab->update_explain_data(join->const_tables); DBUG_RETURN(0); + } + tab->update_explain_data(join->const_tables); + for (ORDER *ord= join->order; ord; ord= ord->next) length++; if (!(join->sortorder= @@ -22057,7 +22133,7 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, Put elements from HAVING, ORDER BY and GROUP BY last to ensure that any reference used in these will resolve to a item that is already calculated */ - param->copy_funcs.concat(&extra_funcs); + param->copy_funcs.append(&extra_funcs); DBUG_RETURN(0); @@ -22534,8 +22610,7 @@ static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab) } if (join_tab->select) { - Item *cond_copy; - UNINIT_VAR(cond_copy); // used when pre_idx_push_select_cond!=NULL + Item *UNINIT_VAR(cond_copy); if (join_tab->select->pre_idx_push_select_cond) cond_copy= cond->copy_andor_structure(thd); if (join_tab->select->cond) @@ -23046,10 +23121,11 @@ void JOIN::clear() /* Print an EXPLAIN line with all NULLs and given message in the 'Extra' column + TODO: is_analyze */ int print_explain_message_line(select_result_sink *result, - uint8 options, + uint8 options, bool is_analyze, uint select_number, const char *select_type, ha_rows *rows, @@ -23080,8 +23156,16 @@ int print_explain_message_line(select_result_sink *result, else item_list.push_back(item_null); + /* `r_rows` */ + if (is_analyze) + item_list.push_back(item_null); + /* `filtered` */ - if (options & DESCRIBE_EXTENDED) + if (is_analyze || options & DESCRIBE_EXTENDED) + item_list.push_back(item_null); + + /* `r_filtered` */ + if (is_analyze) item_list.push_back(item_null); /* `Extra` */ @@ -23132,7 +23216,7 @@ void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line) */ int print_explain_row(select_result_sink *result, - uint8 options, + uint8 options, bool is_analyze, uint select_number, const char *select_type, const char *table_name, @@ -23143,6 +23227,8 @@ int print_explain_row(select_result_sink *result, const char *key_len, const char *ref, ha_rows *rows, + ha_rows *r_rows, + double r_filtered, const char *extra) { Item *item_null= new Item_null(); @@ -23188,12 +23274,28 @@ int print_explain_row(select_result_sink *result, } else item_list.push_back(item_null); + + /* 'r_rows' */ + if (is_analyze) + { + if (r_rows) + { + item_list.push_back(new Item_int(*r_rows, + MY_INT64_NUM_DECIMAL_DIGITS)); + } + else + item_list.push_back(item_null); + } /* 'filtered' */ const double filtered=100.0; - if (options & DESCRIBE_EXTENDED) + if (options & DESCRIBE_EXTENDED || is_analyze) item_list.push_back(new Item_float(filtered, 2)); + /* 'r_filtered' */ + if (is_analyze) + item_list.push_back(new Item_float(r_filtered, 2)); + /* 'Extra' */ if (extra) item_list.push_back(new Item_string_sys(extra)); @@ -23269,7 +23371,7 @@ int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly, /* rows */ item_list.push_back(item_null); /* extra */ - if (select_lex->master_unit()->global_parameters->order_list.first) + if (select_lex->master_unit()->global_parameters()->order_list.first) item_list.push_back(new Item_string_sys("Using filesort", 14)); else item_list.push_back(new Item_string_sys("", 0)); @@ -23318,6 +23420,431 @@ void append_possible_keys(String *str, TABLE *table, key_map possible_keys) } } +// TODO: this function is only applicable for the first non-const optimization +// join tab. +void JOIN_TAB::update_explain_data(uint idx) +{ + if (this == first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS) + join->const_tables && + join->select_lex->select_number != INT_MAX && + join->select_lex->select_number != UINT_MAX) + { + Explain_table_access *eta= new Explain_table_access(); + JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); + save_explain_data(eta, join->const_table_map, join->select_distinct, first_top_tab); + + Explain_select *sel= join->thd->lex->explain->get_select(join->select_lex->select_number); + idx -= my_count_bits(join->eliminated_tables); + sel->replace_table(idx, eta); + } +} + + +void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tables, + bool distinct, JOIN_TAB *first_top_tab) +{ + int quick_type; + const CHARSET_INFO *cs= system_charset_info; + + JOIN_TAB *tab= this; + THD *thd=join->thd; + + TABLE *table=tab->table; + TABLE_LIST *table_list= tab->table->pos_in_table_list; + char buff4[512]; + my_bool key_read; + char table_name_buffer[SAFE_NAME_LEN]; + String tmp4(buff4,sizeof(buff4),cs); + KEY *key_info= 0; + uint key_len= 0; + tmp4.length(0); + quick_type= -1; + QUICK_SELECT_I *quick= NULL; + + eta->key.set(thd->mem_root, NULL, (uint)-1); + eta->quick_info= NULL; + + tab->tracker= &eta->tracker; + tab->jbuf_tracker= &eta->jbuf_tracker; + + /* id */ + if (tab->bush_root_tab) + { + JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start; + eta->sjm_nest_select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier(); + } + else + eta->sjm_nest_select_id= 0; + + /* select_type is kept in Explain_select */ + + /* table */ + if (table->derived_select_number) + { + /* Derived table name generation */ + int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, + "<derived%u>", + table->derived_select_number); + eta->table_name.copy(table_name_buffer, len, cs); + } + else if (tab->bush_children) + { + JOIN_TAB *ctab= tab->bush_children->start; + /* table */ + int len= my_snprintf(table_name_buffer, + sizeof(table_name_buffer)-1, + "<subquery%d>", + ctab->emb_sj_nest->sj_subq_pred->get_identifier()); + eta->table_name.copy(table_name_buffer, len, cs); + } + else + { + TABLE_LIST *real_table= table->pos_in_table_list; + /* + When multi-table UPDATE/DELETE does updates/deletes to a VIEW, the view + is merged in a certain particular way (grep for DT_MERGE_FOR_INSERT). + + As a result, view's underlying tables have $tbl->pos_in_table_list={view}. + We don't want to print view name in EXPLAIN, we want underlying table's + alias (like specified in the view definition). + */ + if (real_table->merged_for_insert) + { + TABLE_LIST *view_child= real_table->view->select_lex.table_list.first; + for (;view_child; view_child= view_child->next_local) + { + if (view_child->table == table) + { + real_table= view_child; + break; + } + } + } + eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs); + } + + /* "partitions" column */ + { +#ifdef WITH_PARTITION_STORAGE_ENGINE + partition_info *part_info; + if (!table->derived_select_number && + (part_info= table->part_info)) + { + make_used_partitions_str(part_info, &eta->used_partitions); + eta->used_partitions_set= true; + } + else + eta->used_partitions_set= false; +#else + /* just produce empty column if partitioning is not compiled in */ + eta->used_partitions_set= false; +#endif + } + + /* "type" column */ + enum join_type tab_type= tab->type; + if ((tab->type == JT_ALL || tab->type == JT_HASH) && + tab->select && tab->select->quick && tab->use_quick != 2) + { + quick= tab->select->quick; + quick_type= tab->select->quick->get_type(); + if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || + (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) + tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; + else + tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; + } + eta->type= tab_type; + + /* Build "possible_keys" value */ + append_possible_keys(&eta->possible_keys_str, table, tab->keys); + + /* Build "key", "key_len", and "ref" */ + if (tab_type == JT_NEXT) + { + key_info= table->key_info+tab->index; + key_len= key_info->key_length; + } + else if (tab->ref.key_parts) + { + key_info= tab->get_keyinfo_by_key_no(tab->ref.key); + key_len= tab->ref.key_length; + } + + /* + In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type + that still have quick selects. + */ + if (tab->select && tab->select->quick && tab_type != JT_CONST) + { + eta->quick_info= tab->select->quick->get_explain(thd->mem_root); + } + + if (key_info) /* 'index' or 'ref' access */ + { + eta->key.set(thd->mem_root, key_info->name, key_len); + + if (tab->ref.key_parts && tab_type != JT_FT) + { + store_key **ref=tab->ref.key_copy; + for (uint kp= 0; kp < tab->ref.key_parts; kp++) + { + if (tmp4.length()) + tmp4.append(','); + + if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map) + tmp4.append("const"); + else + { + tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); + ref++; + } + } + } + } + + if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */ + { + eta->hash_next_key.set(thd->mem_root, + table->key_info[tab->index].name, + table->key_info[tab->index].key_length); + } + + if (key_info) + { + if (key_info && tab_type != JT_NEXT) + { + eta->ref.copy(tmp4); + eta->ref_set= true; + } + else + eta->ref_set= false; + } + else + { + if (table_list && /* SJM bushes don't have table_list */ + table_list->schema_table && + table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) + { + IS_table_read_plan *is_table_read_plan= table_list->is_table_read_plan; + const char *tmp_buff; + int f_idx; + StringBuffer<64> key_name_buf; + if (is_table_read_plan->trivial_show_command || + is_table_read_plan->has_db_lookup_value()) + { + /* The "key" has the name of the column referring to the database */ + f_idx= table_list->schema_table->idx_field1; + tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; + key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); + } + if (is_table_read_plan->trivial_show_command || + is_table_read_plan->has_table_lookup_value()) + { + if (is_table_read_plan->trivial_show_command || + is_table_read_plan->has_db_lookup_value()) + key_name_buf.append(','); + + f_idx= table_list->schema_table->idx_field2; + tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; + key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); + } + + if (key_name_buf.length()) + eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1); + } + eta->ref_set= false; + } + + /* "rows" */ + if (table_list /* SJM bushes don't have table_list */ && + table_list->schema_table) + { + /* I_S tables have rows=extra=NULL */ + eta->rows_set= false; + eta->filtered_set= false; + } + else + { + double examined_rows= tab->get_examined_rows(); + + eta->rows_set= true; + eta->rows= (ha_rows) examined_rows; + + /* "filtered" */ + float f= 0.0; + if (examined_rows) + { + double pushdown_cond_selectivity= tab->cond_selectivity; + if (pushdown_cond_selectivity == 1.0) + f= (float) (100.0 * tab->records_read / examined_rows); + else + f= (float) (100.0 * pushdown_cond_selectivity); + } + set_if_smaller(f, 100.0); + eta->filtered_set= true; + eta->filtered= f; + } + + /* Build "Extra" field and save it */ + key_read=table->key_read; + if ((tab_type == JT_NEXT || tab_type == JT_CONST) && + table->covering_keys.is_set(tab->index)) + key_read=1; + if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && + !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row) + key_read=1; + + if (tab->info) + { + eta->push_extra(tab->info); + } + else if (tab->packed_info & TAB_INFO_HAVE_VALUE) + { + if (tab->packed_info & TAB_INFO_USING_INDEX) + eta->push_extra(ET_USING_INDEX); + if (tab->packed_info & TAB_INFO_USING_WHERE) + eta->push_extra(ET_USING_WHERE); + if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) + eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); + } + else + { + uint keyno= MAX_KEY; + if (tab->ref.key_parts) + keyno= tab->ref.key; + else if (tab->select && quick) + keyno = quick->index; + + if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno && + table->file->pushed_idx_cond) + eta->push_extra(ET_USING_INDEX_CONDITION); + else if (tab->cache_idx_cond) + eta->push_extra(ET_USING_INDEX_CONDITION_BKA); + + if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || + quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) + { + eta->push_extra(ET_USING); + } + if (tab->select) + { + if (tab->use_quick == 2) + { + eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD); + eta->range_checked_map= tab->keys; + } + else if (tab->select->cond || + (tab->cache_select && tab->cache_select->cond)) + { + const COND *pushed_cond= tab->table->file->pushed_cond; + + if ((tab->table->file->ha_table_flags() & + HA_CAN_TABLE_CONDITION_PUSHDOWN) && + pushed_cond) + { + eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION); + } + else + eta->push_extra(ET_USING_WHERE); + } + } + if (table_list /* SJM bushes don't have table_list */ && + table_list->schema_table && + table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) + { + if (!table_list->table_open_method) + eta->push_extra(ET_SKIP_OPEN_TABLE); + else if (table_list->table_open_method == OPEN_FRM_ONLY) + eta->push_extra(ET_OPEN_FRM_ONLY); + else + eta->push_extra(ET_OPEN_FULL_TABLE); + /* psergey-note: the following has a bug.*/ + if (table_list->is_table_read_plan->trivial_show_command || + (table_list->is_table_read_plan->has_db_lookup_value() && + table_list->is_table_read_plan->has_table_lookup_value())) + eta->push_extra(ET_SCANNED_0_DATABASES); + else if (table_list->is_table_read_plan->has_db_lookup_value() || + table_list->is_table_read_plan->has_table_lookup_value()) + eta->push_extra(ET_SCANNED_1_DATABASE); + else + eta->push_extra(ET_SCANNED_ALL_DATABASES); + } + if (key_read) + { + if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + QUICK_GROUP_MIN_MAX_SELECT *qgs= + (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick; + eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY); + eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning(); + } + else + eta->push_extra(ET_USING_INDEX); + } + if (table->reginfo.not_exists_optimize) + eta->push_extra(ET_NOT_EXISTS); + + if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE) + { + explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick), + &eta->mrr_type); + if (eta->mrr_type.length() > 0) + eta->push_extra(ET_USING_MRR); + } + + if (distinct & test_all_bits(prefix_tables, join->select_list_used_tables)) + eta->push_extra(ET_DISTINCT); + if (tab->loosescan_match_tab) + { + eta->push_extra(ET_LOOSESCAN); + } + + if (tab->first_weedout_table) + eta->push_extra(ET_START_TEMPORARY); + if (tab->check_weed_out_table) + eta->push_extra(ET_END_TEMPORARY); + else if (tab->do_firstmatch) + { + if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1) + eta->push_extra(ET_FIRST_MATCH); + else + { + eta->push_extra(ET_FIRST_MATCH); + TABLE *prev_table=tab->do_firstmatch->table; + if (prev_table->derived_select_number) + { + char namebuf[NAME_LEN]; + /* Derived table name generation */ + int len= my_snprintf(namebuf, sizeof(namebuf)-1, + "<derived%u>", + prev_table->derived_select_number); + eta->firstmatch_table_name.append(namebuf, len); + } + else + eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias); + } + } + + for (uint part= 0; part < tab->ref.key_parts; part++) + { + if (tab->ref.cond_guards[part]) + { + eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); + break; + } + } + + if (tab->cache) + { + eta->push_extra(ET_USING_JOIN_BUFFER); + tab->cache->save_explain_data(&eta->bka_type); + } + } +} /* Save Query Plan Footprint @@ -23332,9 +23859,6 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, { Explain_node *explain_node; JOIN *join= this; /* Legacy: this code used to be a non-member function */ - THD *thd=join->thd; - const CHARSET_INFO *cs= system_charset_info; - int quick_type; int error= 0; DBUG_ENTER("JOIN::save_explain_data_intern"); DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s", @@ -23375,27 +23899,9 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab; tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab)) { - uint select_id; - if (tab->bush_root_tab) - { - JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start; - select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier(); - } - else - select_id= join->select_lex->select_number; - TABLE *table=tab->table; - TABLE_LIST *table_list= tab->table->pos_in_table_list; - char buff4[512]; - my_bool key_read; - char table_name_buffer[SAFE_NAME_LEN]; - String tmp4(buff4,sizeof(buff4),cs); - KEY *key_info= 0; - uint key_len= 0; - tmp4.length(0); - quick_type= -1; - QUICK_SELECT_I *quick= NULL; JOIN_TAB *saved_join_tab= NULL; + TABLE *table=tab->table; /* Don't show eliminated tables */ if (table->map & join->eliminated_tables) @@ -23413,379 +23919,20 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, Explain_table_access *eta= new (output->mem_root) Explain_table_access; xpl_sel->add_table(eta); - eta->key.set(thd->mem_root, NULL, (uint)-1); - eta->quick_info= NULL; - - /* id */ - if (tab->bush_root_tab) - eta->sjm_nest_select_id= select_id; - else - eta->sjm_nest_select_id= 0; - - /* select_type */ - xpl_sel->select_type= join->select_lex->type; - - /* table */ - if (table->derived_select_number) - { - /* Derived table name generation */ - int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, - "<derived%u>", - table->derived_select_number); - eta->table_name.copy(table_name_buffer, len, cs); - } - else if (tab->bush_children) - { - JOIN_TAB *ctab= tab->bush_children->start; - /* table */ - int len= my_snprintf(table_name_buffer, - sizeof(table_name_buffer)-1, - "<subquery%d>", - ctab->emb_sj_nest->sj_subq_pred->get_identifier()); - eta->table_name.copy(table_name_buffer, len, cs); - } - else - { - TABLE_LIST *real_table= table->pos_in_table_list; - eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs); - } - - /* "partitions" column */ - { -#ifdef WITH_PARTITION_STORAGE_ENGINE - partition_info *part_info; - if (!table->derived_select_number && - (part_info= table->part_info)) - { - make_used_partitions_str(part_info, &eta->used_partitions); - eta->used_partitions_set= true; - } - else - eta->used_partitions_set= false; -#else - /* just produce empty column if partitioning is not compiled in */ - eta->used_partitions_set= false; -#endif - } - - /* "type" column */ - enum join_type tab_type= tab->type; - if ((tab->type == JT_ALL || tab->type == JT_HASH) && - tab->select && tab->select->quick && tab->use_quick != 2) - { - quick= tab->select->quick; - quick_type= tab->select->quick->get_type(); - if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || - (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || - (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || - (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) - tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; - else - tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; - } - eta->type= tab_type; - - /* Build "possible_keys" value */ - append_possible_keys(&eta->possible_keys_str, table, tab->keys); - - /* Build "key", "key_len", and "ref" */ - if (tab_type == JT_NEXT) - { - key_info= table->key_info+tab->index; - key_len= key_info->key_length; - } - else if (tab->ref.key_parts) - { - key_info= tab->get_keyinfo_by_key_no(tab->ref.key); - key_len= tab->ref.key_length; - } - - /* - In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type - that still have quick selects. - */ - if (tab->select && tab->select->quick && tab_type != JT_CONST) - { - eta->quick_info= tab->select->quick->get_explain(thd->mem_root); - } - - if (key_info) /* 'index' or 'ref' access */ - { - eta->key.set(thd->mem_root, key_info->name, key_len); - - if (tab->ref.key_parts && tab_type != JT_FT) - { - store_key **ref=tab->ref.key_copy; - for (uint kp= 0; kp < tab->ref.key_parts; kp++) - { - if (tmp4.length()) - tmp4.append(','); - - if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map) - tmp4.append("const"); - else - { - tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); - ref++; - } - } - } - } - - if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */ - { - eta->hash_next_key.set(thd->mem_root, - table->key_info[tab->index].name, - table->key_info[tab->index].key_length); - } - - if (key_info) - { - if (key_info && tab_type != JT_NEXT) - { - eta->ref.copy(tmp4); - eta->ref_set= true; - } - else - eta->ref_set= false; - } - else - { - if (table_list && /* SJM bushes don't have table_list */ - table_list->schema_table && - table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) - { - const char *tmp_buff; - int f_idx; - StringBuffer<64> key_name_buf; - if (table_list->has_db_lookup_value) - { - /* The "key" has the name of the column referring to the database */ - f_idx= table_list->schema_table->idx_field1; - tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; - key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); - } - if (table_list->has_table_lookup_value) - { - if (table_list->has_db_lookup_value) - key_name_buf.append(','); - - f_idx= table_list->schema_table->idx_field2; - tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; - key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); - } - - if (key_name_buf.length()) - eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1); - } - eta->ref_set= false; - } - - /* "rows" */ - if (table_list /* SJM bushes don't have table_list */ && - table_list->schema_table) - { - /* I_S tables have rows=extra=NULL */ - eta->rows_set= false; - eta->filtered_set= false; - } - else - { - double examined_rows= tab->get_examined_rows(); - eta->rows_set= true; - eta->rows= (ha_rows) examined_rows; + tab->save_explain_data(eta, used_tables, distinct, first_top_tab); - /* "filtered" */ - float f= 0.0; - if (examined_rows) - { - double pushdown_cond_selectivity= tab->cond_selectivity; - if (pushdown_cond_selectivity == 1.0) - f= (float) (100.0 * tab->records_read / examined_rows); - else - f= (float) (100.0 * pushdown_cond_selectivity); - } - set_if_smaller(f, 100.0); - eta->filtered_set= true; - eta->filtered= f; - } - - /* Build "Extra" field and save it */ - key_read=table->key_read; - if ((tab_type == JT_NEXT || tab_type == JT_CONST) && - table->covering_keys.is_set(tab->index)) - key_read=1; - if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && - !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row) - key_read=1; - - if (tab->info) + if (need_tmp_table) { - eta->push_extra(tab->info); + need_tmp_table=0; + xpl_sel->using_temporary= true; } - else if (tab->packed_info & TAB_INFO_HAVE_VALUE) + if (need_order) { - if (tab->packed_info & TAB_INFO_USING_INDEX) - eta->push_extra(ET_USING_INDEX); - if (tab->packed_info & TAB_INFO_USING_WHERE) - eta->push_extra(ET_USING_WHERE); - if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) - eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); + need_order=0; + xpl_sel->using_filesort= true; } - else - { - uint keyno= MAX_KEY; - if (tab->ref.key_parts) - keyno= tab->ref.key; - else if (tab->select && quick) - keyno = quick->index; - - if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno && - table->file->pushed_idx_cond) - eta->push_extra(ET_USING_INDEX_CONDITION); - else if (tab->cache_idx_cond) - eta->push_extra(ET_USING_INDEX_CONDITION_BKA); - - if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || - quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) - { - eta->push_extra(ET_USING); - } - if (tab->select) - { - if (tab->use_quick == 2) - { - eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD); - eta->range_checked_map= tab->keys; - } - else if (tab->select->cond || - (tab->cache_select && tab->cache_select->cond)) - { - const COND *pushed_cond= tab->table->file->pushed_cond; - - if (thd->use_cond_push(tab->table->file) && pushed_cond) - { - eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION); - /* - psergey-todo: what to do? This was useful with NDB only. - - if (explain_flags & DESCRIBE_EXTENDED) - { - extra.append(STRING_WITH_LEN(": ")); - ((COND *)pushed_cond)->print(&extra, QT_ORDINARY); - } - */ - } - else - eta->push_extra(ET_USING_WHERE); - } - } - if (table_list /* SJM bushes don't have table_list */ && - table_list->schema_table && - table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) - { - if (!table_list->table_open_method) - eta->push_extra(ET_SKIP_OPEN_TABLE); - else if (table_list->table_open_method == OPEN_FRM_ONLY) - eta->push_extra(ET_OPEN_FRM_ONLY); - else - eta->push_extra(ET_OPEN_FULL_TABLE); - /* psergey-note: the following has a bug.*/ - if (table_list->has_db_lookup_value && - table_list->has_table_lookup_value) - eta->push_extra(ET_SCANNED_0_DATABASES); - else if (table_list->has_db_lookup_value || - table_list->has_table_lookup_value) - eta->push_extra(ET_SCANNED_1_DATABASE); - else - eta->push_extra(ET_SCANNED_ALL_DATABASES); - } - if (key_read) - { - if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) - { - QUICK_GROUP_MIN_MAX_SELECT *qgs= - (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick; - eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY); - eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning(); - } - else - eta->push_extra(ET_USING_INDEX); - } - if (table->reginfo.not_exists_optimize) - eta->push_extra(ET_NOT_EXISTS); - - if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE) - { - explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick), - &eta->mrr_type); - if (eta->mrr_type.length() > 0) - eta->push_extra(ET_USING_MRR); - } - - if (need_tmp_table) - { - need_tmp_table=0; - xpl_sel->using_temporary= true; - } - if (need_order) - { - need_order=0; - xpl_sel->using_filesort= true; - } - if (distinct & test_all_bits(used_tables, - join->select_list_used_tables)) - eta->push_extra(ET_DISTINCT); - if (tab->loosescan_match_tab) - { - eta->push_extra(ET_LOOSESCAN); - } - - if (tab->first_weedout_table) - eta->push_extra(ET_START_TEMPORARY); - if (tab->check_weed_out_table) - eta->push_extra(ET_END_TEMPORARY); - else if (tab->do_firstmatch) - { - if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1) - eta->push_extra(ET_FIRST_MATCH); - else - { - eta->push_extra(ET_FIRST_MATCH); - TABLE *prev_table=tab->do_firstmatch->table; - if (prev_table->derived_select_number) - { - char namebuf[NAME_LEN]; - /* Derived table name generation */ - int len= my_snprintf(namebuf, sizeof(namebuf)-1, - "<derived%u>", - prev_table->derived_select_number); - eta->firstmatch_table_name.append(namebuf, len); - } - else - eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias); - } - } - - for (uint part= 0; part < tab->ref.key_parts; part++) - { - if (tab->ref.cond_guards[part]) - { - eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); - break; - } - } - if (tab->cache) - { - eta->push_extra(ET_USING_JOIN_BUFFER); - tab->cache->save_explain_data(&eta->bka_type); - } - } - if (saved_join_tab) tab= saved_join_tab; @@ -23900,16 +24047,19 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) if (unit->is_union()) { - unit->fake_select_lex->select_number= FAKE_SELECT_LEX_ID; // jost for initialization - unit->fake_select_lex->type= "UNION RESULT"; - unit->fake_select_lex->options|= SELECT_DESCRIBE; + if (unit->union_needs_tmp_table()) + { + unit->fake_select_lex->select_number= FAKE_SELECT_LEX_ID; // just for initialization + unit->fake_select_lex->type= "UNION RESULT"; + unit->fake_select_lex->options|= SELECT_DESCRIBE; + } if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE))) res= unit->exec(); } else { thd->lex->current_select= first; - unit->set_limit(unit->global_parameters); + unit->set_limit(unit->global_parameters()); res= mysql_select(thd, &first->ref_pointer_array, first->table_list.first, first->with_wild, first->item_list, @@ -24393,28 +24543,34 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) /** - change select_result object of JOIN. + Change the select_result object of the JOIN. - @param res new select_result object + If old_result is not used, forward the call to the current + select_result in case it is a wrapper around old_result. - @retval - FALSE OK - @retval - TRUE error + Call prepare() and prepare2() on the new select_result if we decide + to use it. + + @param new_result New select_result object + @param old_result Old select_result object (NULL to force change) + + @retval false Success + @retval true Error */ -bool JOIN::change_result(select_result *res) +bool JOIN::change_result(select_result *new_result, select_result *old_result) { DBUG_ENTER("JOIN::change_result"); - result= res; - if (tmp_join) - tmp_join->result= res; - if (!procedure && (result->prepare(fields_list, select_lex->master_unit()) || - result->prepare2())) + if (old_result == NULL || result == old_result) { - DBUG_RETURN(TRUE); + result= new_result; + if (result->prepare(fields_list, select_lex->master_unit()) || + result->prepare2()) + DBUG_RETURN(true); /* purecov: inspected */ + DBUG_RETURN(false); } - DBUG_RETURN(FALSE); + else + DBUG_RETURN(result->change_result(new_result)); } @@ -24672,6 +24828,114 @@ void JOIN::cache_const_exprs() } } + +/* + Get a cost of reading rows_limit rows through index keynr. + + @detail + - If there is a quick select, we try to use it. + - if there is a ref(const) access, we try to use it, too. + - quick and ref(const) use different cost formulas, so if both are possible + we should make a cost-based choice. + + @param tab JOIN_TAB with table access (is NULL for single-table + UPDATE/DELETE) + @param read_time OUT Cost of reading using quick or ref(const) access. + + + @return + true There was a possible quick or ref access, its cost is in the OUT + parameters. + false No quick or ref(const) possible (and so, the caller will attempt + to use a full index scan on this index). +*/ + +static bool get_range_limit_read_cost(const JOIN_TAB *tab, + const TABLE *table, + uint keynr, + ha_rows rows_limit, + double *read_time) +{ + bool res= false; + /* + We need to adjust the estimates if we had a quick select (or ref(const)) on + index keynr. + */ + if (table->quick_keys.is_set(keynr)) + { + /* + Start from quick select's rows and cost. These are always cheaper than + full index scan/cost. + */ + double best_rows= table->quick_rows[keynr]; + double best_cost= table->quick_costs[keynr]; + + /* + Check if ref(const) access was possible on this index. + */ + if (tab) + { + key_part_map const_parts= 0; + key_part_map map= 1; + uint kp; + /* Find how many key parts would be used by ref(const) */ + for (kp=0; kp < MAX_REF_PARTS; map=map << 1, kp++) + { + if (!(table->const_key_parts[keynr] & map)) + break; + const_parts |= map; + } + + if (kp > 0) + { + ha_rows ref_rows; + /* + Two possible cases: + 1. ref(const) uses the same #key parts as range access. + 2. ref(const) uses fewer key parts, becasue there is a + range_cond(key_part+1). + */ + if (kp == table->quick_key_parts[keynr]) + ref_rows= table->quick_rows[keynr]; + else + ref_rows= table->key_info[keynr].actual_rec_per_key(kp-1); + + if (ref_rows > 0) + { + double tmp= ref_rows; + /* Reuse the cost formula from best_access_path: */ + set_if_smaller(tmp, (double) tab->join->thd->variables.max_seeks_for_key); + if (table->covering_keys.is_set(keynr)) + tmp= table->file->keyread_time(keynr, 1, (ha_rows) tmp); + else + tmp= table->file->read_time(keynr, 1, + (ha_rows) MY_MIN(tmp,tab->worst_seeks)); + if (tmp < best_cost) + { + best_cost= tmp; + best_rows= ref_rows; + } + } + } + } + + if (best_rows > rows_limit) + { + /* + LIMIT clause specifies that we will need to read fewer records than + quick select will return. Assume that quick select's cost is + proportional to the number of records we need to return (e.g. if we + only need 1/3rd of records, it will cost us 1/3rd of quick select's + read time) + */ + best_cost *= rows_limit / best_rows; + } + *read_time= best_cost; + res= true; + } + return res; +} + /** Find a cheaper access key than a given @a key @@ -24765,6 +25029,11 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, } else read_time= table->file->scan_time(); + + /* + TODO: add cost of sorting here. + */ + read_time += COST_EPS; /* Calculate the selectivity of the ref_key for REF_ACCESS. For @@ -24924,6 +25193,14 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ index_scan_time= select_limit/rec_per_key * MY_MIN(rec_per_key, table->file->scan_time()); + double range_scan_time; + if (get_range_limit_read_cost(tab, table, nr, select_limit, + &range_scan_time)) + { + if (range_scan_time < index_scan_time) + index_scan_time= range_scan_time; + } + if ((ref_key < 0 && (group || table->force_index || is_covering)) || index_scan_time < read_time) { @@ -25078,15 +25355,18 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select, return MAX_KEY; } + /* - Count how much times conditions are true for several first rows of the table + Count how many times the specified conditions are true for first rows_to_read + rows of the table. - @param thd thread handle - @param rows_to_read how much rows to check - @param table table which should be checked - @conds conds list of conditions and countars for them + @param thd Thread handle + @param rows_to_read How many rows to sample + @param table Table to use + @conds conds INOUT List of conditions and counters for them - @return number of really checked rows or 0 in case of error or empty table + @return Number of we've checked. It can be equal or less than rows_to_read. + 0 is returned for error or when the table had no rows. */ ulong check_selectivity(THD *thd, |