From 6cc8d986ec89bba642d89149617e1baa3aa0feb0 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 3 Aug 2007 03:14:05 +0500 Subject: WL#3732 Information schema optimization client/mysqldump.c: table type compare is changed to case insensitive mysql-test/r/information_schema.result: test result mysql-test/r/information_schema_db.result: result fix mysql-test/suite/ndb/r/ndb_alter_table.result: result fix mysql-test/suite/ndb/r/ndb_temporary.result: result fix mysql-test/t/information_schema.test: test case sql/ha_ndbcluster.cc: char* variables are changed to LEX_STRING sql/ha_ndbcluster.h: char* variables are changed to LEX_STRING sql/ha_ndbcluster_binlog.cc: char* variables are changed to LEX_STRING sql/handler.cc: char* variables are changed to LEX_STRING sql/handler.h: char* variables are changed to LEX_STRING sql/sql_base.cc: Modified functions which are used during open table process according to table opening method and requested_object. sql/sql_select.cc: Add support for I_S tables into select_describe() function sql/sql_show.cc: 1. Added initialization of 'open_method' to 'st_field_info' structs. 2. Added initialization of 'i_s_requested_object' to 'ST_SCHEMA_TABLE' structs. 3. New function which calculates database name and table name values from 'where' condition if it's possible void get_lookup_field_values(THD *thd, COND *cond, TABLE_LIST *table, LOOKUP_FIELD_VALUES *lookup_field_vals); 4. New function which set table open method setup_table_open_method(TABLE_LIST *tables, ST_SCHEMA_TABLE *schema_table, enum enum_schema_tables schema_table_idx) 5. New function int make_db_list(THD *thd, List *files, LOOKUP_FIELD_VALUES *lookup_field_vals, bool *with_i_schema) 6. New function int make_table_name_list(THD *thd, List *files, LEX *lex, LOOKUP_FIELD_VALUES *lookup_field_vals, bool with_i_schema, LEX_STRING *db_name) 7. Modified 'get_all_tables' function according to new schema(see wl#3732). sql/sql_show.h: char* variables are changed to LEX_STRING sql/table.cc: Modified functions which are used during open table process according to table opening method and requested_object. sql/table.h: 1. added new constants(open_method) #define SKIP_OPEN_TABLE 0 #define OPEN_FRM_ONLY 1 #define OPEN_FULL_TABLE 2 2. Added new field 'open_method' into struct st_field_info; uint open_method; 3. Added new field into ST_SCHEMA_TABLE struct uint i_s_requested_object; /* the object we need to open(TABLE | VIEW) */. 4. Added new field to TABLE_LIST struct. uint i_s_requested_object; This field is set from ST_SCHEMA_TABLE.i_s_requested_object for processed table before opening. 5. Added new fields to TABLE_LIST struct, used for 'explain select' for I_S table bool has_db_lookup_value; bool has_table_lookup_value; uint table_open_method; sql/unireg.h: added new constants --- sql/sql_select.cc | 106 ++++++++++++++++++++++++++++++++++++++---------------- 1 file changed, 76 insertions(+), 30 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9ea67af1bf9..2d383ee1bfc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1625,6 +1625,10 @@ JOIN::exec() DBUG_VOID_RETURN; } + if ((this->select_lex->options & OPTION_SCHEMA_TABLE) && + get_schema_tables_result(this, PROCESSED_BY_JOIN_EXEC)) + DBUG_VOID_RETURN; + if (select_options & SELECT_DESCRIBE) { /* @@ -1670,13 +1674,6 @@ JOIN::exec() */ curr_join->examined_rows= 0; - if ((curr_join->select_lex->options & OPTION_SCHEMA_TABLE) && - !thd->lex->describe && - get_schema_tables_result(curr_join, PROCESSED_BY_JOIN_EXEC)) - { - DBUG_VOID_RETURN; - } - /* Create a tmp table if distinct or if the sort is too complicated */ if (need_tmp) { @@ -12929,7 +12926,6 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, /* Fill schema tables with data before filesort if it's necessary */ if ((join->select_lex->options & OPTION_SCHEMA_TABLE) && - !thd->lex->describe && get_schema_tables_result(join, PROCESSED_BY_CREATE_SORT_INDEX)) goto err; @@ -15382,6 +15378,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, { JOIN_TAB *tab=join->join_tab+i; TABLE *table=tab->table; + TABLE_LIST *table_list= tab->table->pos_in_table_list; char buff[512]; char buff1[512], buff2[512], buff3[512]; char keylen_str_buf[64]; @@ -15517,37 +15514,68 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, } else { - item_list.push_back(item_null); + if (table_list->schema_table && + table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) + { + const char *tmp_buff; + int f_idx; + if (table_list->has_db_lookup_value) + { + f_idx= table_list->schema_table->idx_field1; + tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; + tmp2.append(tmp_buff, strlen(tmp_buff), cs); + } + if (table_list->has_table_lookup_value) + { + if (table_list->has_db_lookup_value) + tmp2.append(','); + f_idx= table_list->schema_table->idx_field2; + tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; + tmp2.append(tmp_buff, strlen(tmp_buff), cs); + } + if (tmp2.length()) + item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs)); + else + item_list.push_back(item_null); + } + else + item_list.push_back(item_null); item_list.push_back(item_null); item_list.push_back(item_null); } /* Add "rows" field to item_list. */ - ha_rows examined_rows; - if (tab->select && tab->select->quick) - examined_rows= tab->select->quick->records; - else if (tab->type == JT_NEXT || tab->type == JT_ALL) - examined_rows= tab->table->file->records(); + if (table_list->schema_table) + { + item_list.push_back(item_null); + } else - examined_rows=(ha_rows)join->best_positions[i].records_read; - - item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, - MY_INT64_NUM_DECIMAL_DIGITS)); - - /* Add "filtered" field to item_list. */ - if (join->thd->lex->describe & DESCRIBE_EXTENDED) { - Item_float *filtered; - float f; - if (examined_rows) - f= (float) (100.0 * join->best_positions[i].records_read / - examined_rows); + ha_rows examined_rows; + if (tab->select && tab->select->quick) + examined_rows= tab->select->quick->records; + else if (tab->type == JT_NEXT || tab->type == JT_ALL) + examined_rows= tab->table->file->records(); else - f= 0.0; - item_list.push_back((filtered= new Item_float(f))); - filtered->decimals= 2; - } + examined_rows=(ha_rows)join->best_positions[i].records_read; + + item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, + MY_INT64_NUM_DECIMAL_DIGITS)); + /* Add "filtered" field to item_list. */ + if (join->thd->lex->describe & DESCRIBE_EXTENDED) + { + Item_float *filtered; + float f; + if (examined_rows) + f= (float) (100.0 * join->best_positions[i].records_read / + examined_rows); + else + f= 0.0; + item_list.push_back((filtered= new Item_float(f))); + filtered->decimals= 2; + } + } /* Build "Extra" field and add it to item_list. */ my_bool key_read=table->key_read; @@ -15615,6 +15643,24 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, extra.append(STRING_WITH_LEN("; Using where")); } } + if (table_list->schema_table && + table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) + { + if (!table_list->table_open_method) + extra.append(STRING_WITH_LEN("; Skip_open_table")); + else if (table_list->table_open_method == OPEN_FRM_ONLY) + extra.append(STRING_WITH_LEN("; Open_frm_only")); + else + extra.append(STRING_WITH_LEN("; Open_full_table")); + if (table_list->has_db_lookup_value && + table_list->has_table_lookup_value) + extra.append(STRING_WITH_LEN("; Scanned 0 databases")); + else if (table_list->has_db_lookup_value || + table_list->has_table_lookup_value) + extra.append(STRING_WITH_LEN("; Scanned 1 database")); + else + extra.append(STRING_WITH_LEN("; Scanned all databases")); + } if (key_read) { if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) -- cgit v1.2.1