| List: | Commits | « Previous MessageNext Message » | |
| From: | kgeorge | Date: | January 4 2007 4:20pm |
| Subject: | bk commit into 5.1 tree (gkodinov:1.2359) | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.1 repository of kgeorge. When kgeorge does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-01-04 17:19:57+02:00, gkodinov@stripped +23 -0 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified Currently MySQL allows one to specify what indexes to ignore during join optimization. The scope of the current IGNORE INDEX statement is only the FROM clause, while all other clauses are not affected. However, in certain cases, the optimizer may incorrectly choose an index for sorting and/or grouping, and produce an inefficient query plan. This task provides the means to specify what indexes are ignored for what operation in a more fine-grained manner, thus making it possible to manually force a better plan. We do this by extending the current IGNORE INDEX syntax to: IGNORE INDEX [FOR {JOIN | ORDER | GROUP BY}] so that: - if no FOR is specified, the index will be ignored everywhere. - if MySQL is started with the compatibility option --old then IGNORE INDEX without a FOR clause works as in 5.0 (i.e, the index will only be ignored for JOINs, but can still be used to compute ORDER BY. See the WL#3527 for further details. BitKeeper/deleted/.del-mysqld.cc.rej@stripped, 2006-12-22 12:08:47+02:00, gkodinov@stripped +0 -0 Delete: sql/mysqld.cc.rej BitKeeper/deleted/.del-sql_parse.cc.rej@stripped, 2006-12-22 12:08:55+02:00, gkodinov@stripped +0 -0 Delete: sql/sql_parse.cc.rej BitKeeper/deleted/.del-table.cc.rej@stripped, 2006-12-22 12:09:01+02:00, gkodinov@stripped +0 -0 Delete: sql/table.cc.rej mysql-test/r/group_by.result@stripped, 2007-01-04 17:19:30+02:00, gkodinov@stripped +59 -7 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - test cases mysql-test/t/group_by.test@stripped, 2007-01-04 17:19:31+02:00, gkodinov@stripped +33 -6 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - test cases sql/item.cc@stripped, 2007-01-04 17:19:32+02:00, gkodinov@stripped +1 -1 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/mysql_priv.h@stripped, 2007-01-04 17:19:32+02:00, gkodinov@stripped +0 -1 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/mysqld.cc@stripped, 2007-01-04 17:19:33+02:00, gkodinov@stripped +8 -1 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/opt_range.cc@stripped, 2007-01-04 17:19:34+02:00, gkodinov@stripped +4 -4 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_base.cc@stripped, 2007-01-04 17:19:35+02:00, gkodinov@stripped +13 -29 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_class.h@stripped, 2007-01-04 17:19:36+02:00, gkodinov@stripped +3 -0 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_delete.cc@stripped, 2007-01-04 17:19:36+02:00, gkodinov@stripped +2 -2 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_help.cc@stripped, 2007-01-04 17:19:37+02:00, gkodinov@stripped +1 -1 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_lex.cc@stripped, 2007-01-04 17:19:38+02:00, gkodinov@stripped +113 -24 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_lex.h@stripped, 2007-01-04 17:19:38+02:00, gkodinov@stripped +68 -10 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_parse.cc@stripped, 2007-01-04 17:19:39+02:00, gkodinov@stripped +8 -16 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_select.cc@stripped, 2007-01-04 17:19:40+02:00, gkodinov@stripped +53 -38 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_show.cc@stripped, 2007-01-04 17:19:41+02:00, gkodinov@stripped +2 -4 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_update.cc@stripped, 2007-01-04 17:19:42+02:00, gkodinov@stripped +8 -8 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/sql_yacc.yy@stripped, 2007-01-04 17:19:43+02:00, gkodinov@stripped +78 -54 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/table.cc@stripped, 2007-01-04 17:19:44+02:00, gkodinov@stripped +86 -1 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation sql/table.h@stripped, 2007-01-04 17:19:44+02:00, gkodinov@stripped +6 -2 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation storage/myisam/ha_myisam.cc@stripped, 2007-01-04 17:19:45+02:00, gkodinov@stripped +21 -23 WL#3527: Extend IGNORE INDEX so places where index is ignored can be specified - implementation # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gkodinov # Host: macbook.gmz # Root: /Users/kgeorge/mysql/work/WL3527-5.1-opt --- 1.202/storage/myisam/ha_myisam.cc 2006-11-21 23:15:35 +02:00 +++ 1.203/storage/myisam/ha_myisam.cc 2007-01-04 17:19:45 +02:00 @@ -808,19 +808,18 @@ int ha_myisam::assign_to_keycache(THD* t TABLE_LIST *table_list= table->pos_in_table_list; DBUG_ENTER("ha_myisam::assign_to_keycache"); - /* Check validity of the index references */ - if (table_list->use_index) + table->keys_in_use_for_query.clear_all(); + table->keys_in_use_for_group_by.clear_all(); + table->keys_in_use_for_order_by.clear_all(); + + if (table_list->process_index_hints(table)) { - /* We only come here when the user did specify an index map */ - key_map kmap; - if (get_key_map_from_key_list(&kmap, table, table_list->use_index)) - { - errmsg= thd->net.last_error; - error= HA_ADMIN_FAILED; - goto err; - } - map= kmap.to_ulonglong(); + errmsg= thd->net.last_error; + error= HA_ADMIN_FAILED; + goto err; } + if (!table->keys_in_use_for_query.is_clear_all()) + map= table->keys_in_use_for_query.to_ulonglong(); if ((error= mi_assign_to_key_cache(file, map, new_key_cache))) { @@ -862,20 +861,19 @@ int ha_myisam::preload_keys(THD* thd, HA DBUG_ENTER("ha_myisam::preload_keys"); - /* Check validity of the index references */ - if (table_list->use_index) + table->keys_in_use_for_query.clear_all(); + table->keys_in_use_for_group_by.clear_all(); + table->keys_in_use_for_order_by.clear_all(); + + if (table_list->process_index_hints(table)) { - key_map kmap; - get_key_map_from_key_list(&kmap, table, table_list->use_index); - if (kmap.is_set_all()) - { - errmsg= thd->net.last_error; - error= HA_ADMIN_FAILED; - goto err; - } - if (!kmap.is_clear_all()) - map= kmap.to_ulonglong(); + errmsg= thd->net.last_error; + error= HA_ADMIN_FAILED; + goto err; } + /* Check validity of the index references */ + if (!table->keys_in_use_for_query.is_clear_all()) + map= table->keys_in_use_for_query.to_ulonglong(); mi_extra(file, HA_EXTRA_PRELOAD_BUFFER_SIZE, (void *) &thd->variables.preload_buff_size); --- 1.225/sql/item.cc 2006-12-04 21:02:31 +02:00 +++ 1.226/sql/item.cc 2007-01-04 17:19:32 +02:00 @@ -3828,7 +3828,7 @@ bool Item_field::fix_fields(THD *thd, It { /* First usage of column */ table->used_fields++; // Used to optimize loops - table->used_keys.intersect(field->part_of_key); + table->key_usable_in_read_only.intersect(field->part_of_key); } } } --- 1.464/sql/mysql_priv.h 2006-12-05 11:54:11 +02:00 +++ 1.465/sql/mysql_priv.h 2007-01-04 17:19:32 +02:00 @@ -1957,7 +1957,6 @@ inline void setup_table_map(TABLE *table table->const_table= 0; table->null_row= 0; table->status= STATUS_NO_RECORD; - table->keys_in_use_for_query= table->s->keys_in_use; table->maybe_null= table_list->outer_join; TABLE_LIST *embedding= table_list->embedding; while (!table->maybe_null && embedding) --- 1.599/sql/mysqld.cc 2006-12-05 13:11:42 +02:00 +++ 1.600/sql/mysqld.cc 2007-01-04 17:19:33 +02:00 @@ -4869,7 +4869,8 @@ enum options_mysqld OPT_PORT_OPEN_TIMEOUT, OPT_GENERAL_LOG, OPT_SLOW_LOG, - OPT_MERGE + OPT_MERGE, + OPT_OLD }; @@ -6273,6 +6274,10 @@ The minimum value for this variable is 4 (gptr*) &max_system_variables.net_wait_timeout, 0, GET_ULONG, REQUIRED_ARG, NET_WAIT_TIMEOUT, 1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT), 0, 1, 0}, + { "old", OPT_OLD, "Use compatible behaviour.", + (gptr*) &global_system_variables.opt_old, + (gptr*) &max_system_variables.opt_old, 0, GET_BOOL, NO_ARG, + 0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} }; @@ -7031,6 +7036,8 @@ static void mysql_init_variables(void) when collecting index statistics for MyISAM tables. */ global_system_variables.myisam_stats_method= MI_STATS_METHOD_NULLS_NOT_EQUAL; + + global_system_variables.opt_old= FALSE; /* Variables that depends on compile options */ #ifndef DBUG_OFF --- 1.252/sql/opt_range.cc 2006-12-02 00:11:56 +02:00 +++ 1.253/sql/opt_range.cc 2007-01-04 17:19:34 +02:00 @@ -2109,9 +2109,9 @@ int SQL_SELECT::test_quick_select(THD *t param.key_parts_end=key_parts; /* Calculate cost of full index read for the shortest covering index */ - if (!head->used_keys.is_clear_all()) + if (!head->key_usable_in_read_only.is_clear_all()) { - int key_for_use= find_shortest_key(head, &head->used_keys); + int key_for_use= find_shortest_key(head, &head->key_usable_in_read_only); double key_read_time= (get_index_only_read_time(¶m, records, key_for_use) + (double) records / TIME_FOR_COMPARE); @@ -4646,7 +4646,7 @@ static TRP_RANGE *get_key_scans_params(P param->needed_reg->set_bit(keynr); bool read_index_only= index_read_must_be_used ? TRUE : - (bool) param->table->used_keys.is_set(keynr); + (bool) param->table->key_usable_in_read_only.is_set(keynr); found_records= check_quick_select(param, idx, *key, update_tbl_stats); if (param->is_ror_scan) @@ -8988,7 +8988,7 @@ get_best_group_min_max(PARAM *param, SEL cur_index_info++, cur_index++) { /* Check (B1) - if current index is covering. */ - if (!table->used_keys.is_set(cur_index)) + if (!table->key_usable_in_read_only.is_set(cur_index)) goto next_index; /* --- 1.368/sql/sql_base.cc 2006-12-04 21:02:33 +02:00 +++ 1.369/sql/sql_base.cc 2007-01-04 17:19:35 +02:00 @@ -1792,8 +1792,6 @@ bool reopen_name_locked_table(THD* thd, table->const_table=0; table->null_row= table->maybe_null= table->force_index= 0; table->status=STATUS_NO_RECORD; - table->keys_in_use_for_query= share->keys_in_use; - table->used_keys= share->keys_for_keyread; DBUG_RETURN(FALSE); } @@ -2115,9 +2113,7 @@ TABLE *open_table(THD *thd, TABLE_LIST * table->const_table=0; table->null_row= table->maybe_null= table->force_index= 0; table->status=STATUS_NO_RECORD; - table->keys_in_use_for_query= table->s->keys_in_use; table->insert_values= 0; - table->used_keys= table->s->keys_for_keyread; table->fulltext_searched= 0; table->file->ft_handler= 0; if (table->timestamp_field) @@ -2202,8 +2198,6 @@ static bool reopen_table(TABLE *table) tmp.null_row= table->null_row; tmp.maybe_null= table->maybe_null; tmp.status= table->status; - tmp.keys_in_use_for_query= tmp.s->keys_in_use; - tmp.used_keys= tmp.s->keys_for_keyread; tmp.s->table_map_id= table->s->table_map_id; @@ -3621,7 +3615,7 @@ static void update_field_dependencies(TH been set for all fields (for example for view). */ - table->used_keys.intersect(field->part_of_key); + table->key_usable_in_read_only.intersect(field->part_of_key); table->merge_keys.merge(field->part_of_key); if (thd->mark_used_columns == MARK_COLUMNS_READ) @@ -4798,7 +4792,7 @@ mark_common_columns(THD *thd, TABLE_LIST TABLE *table_1= nj_col_1->table_ref->table; /* Mark field_1 used for table cache. */ bitmap_set_bit(table_1->read_set, field_1->field_index); - table_1->used_keys.intersect(field_1->part_of_key); + table_1->key_usable_in_read_only.intersect(field_1->part_of_key); table_1->merge_keys.merge(field_1->part_of_key); } if (field_2) @@ -4806,7 +4800,7 @@ mark_common_columns(THD *thd, TABLE_LIST TABLE *table_2= nj_col_2->table_ref->table; /* Mark field_2 used for table cache. */ bitmap_set_bit(table_2->read_set, field_2->field_index); - table_2->used_keys.intersect(field_2->part_of_key); + table_2->key_usable_in_read_only.intersect(field_2->part_of_key); table_2->merge_keys.merge(field_2->part_of_key); } @@ -5438,6 +5432,10 @@ bool setup_tables(THD *thd, Name_resolut { TABLE *table= table_list->table; table->pos_in_table_list= table_list; + + table->keys_in_use_for_query= table->keys_in_use_for_group_by= + table->keys_in_use_for_order_by= table->s->keys_in_use; + if (first_select_table && table_list->top_table() == first_select_table) { @@ -5446,25 +5444,11 @@ bool setup_tables(THD *thd, Name_resolut tablenr= 0; } setup_table_map(table, table_list, tablenr); - table->used_keys= table->s->keys_for_keyread; + table->key_usable_in_read_only= table->s->keys_for_keyread; table->merge_keys.clear_all(); - if (table_list->use_index) - { - key_map map; - get_key_map_from_key_list(&map, table, table_list->use_index); - if (map.is_set_all()) - DBUG_RETURN(1); - table->keys_in_use_for_query=map; - } - if (table_list->ignore_index) - { - key_map map; - get_key_map_from_key_list(&map, table, table_list->ignore_index); - if (map.is_set_all()) - DBUG_RETURN(1); - table->keys_in_use_for_query.subtract(map); - } - table->used_keys.intersect(table->keys_in_use_for_query); + table->key_usable_in_read_only= table->s->keys_for_keyread; + if (table_list->process_index_hints(table)) + DBUG_RETURN(1); } if (tablenr > MAX_TABLES) { @@ -5746,7 +5730,7 @@ insert_fields(THD *thd, Name_resolution_ bitmap_set_bit(field->table->read_set, field->field_index); if (table) { - table->used_keys.intersect(field->part_of_key); + table->key_usable_in_read_only.intersect(field->part_of_key); table->merge_keys.merge(field->part_of_key); } if (tables->is_natural_join) @@ -5764,7 +5748,7 @@ insert_fields(THD *thd, Name_resolution_ if (field_table) { thd->used_tables|= field_table->map; - field_table->used_keys.intersect(field->part_of_key); + field_table->key_usable_in_read_only.intersect(field->part_of_key); field_table->merge_keys.merge(field->part_of_key); field_table->used_fields++; } --- 1.332/sql/sql_class.h 2006-12-12 16:01:38 +02:00 +++ 1.333/sql/sql_class.h 2007-01-04 17:19:36 +02:00 @@ -277,6 +277,9 @@ struct system_variables DATE_TIME_FORMAT *datetime_format; DATE_TIME_FORMAT *time_format; my_bool sysdate_is_now; + + /* compatibility options */ + my_bool opt_old; }; --- 1.204/sql/sql_delete.cc 2006-11-30 22:01:24 +02:00 +++ 1.205/sql/sql_delete.cc 2007-01-04 17:19:36 +02:00 @@ -117,7 +117,7 @@ bool mysql_delete(THD *thd, TABLE_LIST * /* Update the table->file->stats.records number */ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); - table->used_keys.clear_all(); + table->key_usable_in_read_only.clear_all(); table->quick_keys.clear_all(); // Can't use 'only index' select=make_select(table, 0, 0, conds, 0, &error); if (error) @@ -548,7 +548,7 @@ multi_delete::initialize_tables(JOIN *jo tbl->no_keyread=1; /* Don't use record cache */ tbl->no_cache= 1; - tbl->used_keys.clear_all(); + tbl->key_usable_in_read_only.clear_all(); if (tbl->file->has_transactions()) transactional_tables= 1; else --- 1.212/sql/sql_lex.cc 2006-12-05 19:52:54 +02:00 +++ 1.213/sql/sql_lex.cc 2007-01-04 17:19:38 +02:00 @@ -70,6 +70,16 @@ static uchar to_upper_lex[]= 208,209,210,211,212,213,214,247,216,217,218,219,220,221,222,255 }; +/* keep in sync with key_usage_type */ +const char * key_usage_type_name[] = +{ + "IGNORE INDEX FOR JOIN", + "IGNORE INDEX FOR GROUP BY", + "IGNORE INDEX FOR ORDER", + "IGNORE INDEX", + "USE INDEX", + "FORCE INDEX" +}; inline int lex_casecmp(const char *s, const char *t, uint len) { @@ -1173,7 +1183,6 @@ void st_select_lex::init_select() group_list.empty(); type= db= 0; having= 0; - use_index_ptr= ignore_index_ptr= 0; table_join_options= 0; in_sum_expr= with_wild= 0; options= 0; @@ -1182,7 +1191,6 @@ void st_select_lex::init_select() when_list.empty(); expr_list.empty(); interval_list.empty(); - use_index.empty(); ftfunc_list_alloc.empty(); inner_sum_func_list= 0; ftfunc_list= &ftfunc_list_alloc; @@ -1397,15 +1405,14 @@ bool st_select_lex_node::inc_in_sum_expr uint st_select_lex_node::get_in_sum_expr() { return 0; } TABLE_LIST* st_select_lex_node::get_table_list() { return 0; } List<Item>* st_select_lex_node::get_item_list() { return 0; } -List<String>* st_select_lex_node::get_use_index() { return 0; } -List<String>* st_select_lex_node::get_ignore_index() { return 0; } -TABLE_LIST *st_select_lex_node::add_table_to_list(THD *thd, Table_ident *table, - LEX_STRING *alias, - ulong table_join_options, - thr_lock_type flags, - List<String> *use_index, - List<String> *ignore_index, - LEX_STRING *option) +TABLE_LIST *st_select_lex_node::add_table_to_list +(THD *thd, + Table_ident *table, + LEX_STRING *alias, + ulong table_join_options, + thr_lock_type flags, + List<key_usage> *hints, + LEX_STRING *option) { return 0; } @@ -1511,19 +1518,6 @@ List<Item>* st_select_lex::get_item_list return &item_list; } - -List<String>* st_select_lex::get_use_index() -{ - return use_index_ptr; -} - - -List<String>* st_select_lex::get_ignore_index() -{ - return ignore_index_ptr; -} - - ulong st_select_lex::get_table_join_options() { return table_join_options; @@ -2260,3 +2254,98 @@ void st_select_lex::fix_prepare_informat are in sql_union.cc */ +/* + Sets the kind of hints to be added by the calls to add_index_hint(). + + SYNOPSIS + set_index_hint_type() + type the kind of hints to be added from now on. + + DESCRIPTION + Used in filling up the tagged hints list. + This list is filled by first setting the kind of the hint as a + context variable and then adding hints of the current kind. + Then the context variable key_usage_type can be reset to the + next hint type. + This function also sets the flag about presence of USE/FORCE INDEX + hints (has_use_index). USE INDEX is different from the other hints : + USE INDEX with an empty list is not the same as not specifying + USE INDEX at all. USE INDEX with an empty index list means + ignore all indexes, not use the indexes as if there is no hint + specified. + Because of that difference USE/FORCE INDEX () is a special case and + cannot be mixed with the other index hints. + + RETURN VALUE + Returns the last hint added to the hints tagged list +*/ +bool st_select_lex::set_index_hint_type(enum key_usage_type type) +{ + key_usage_type= type; + if (has_use_index && index_hints && !index_hints->elements) + { + my_error (ER_WRONG_USAGE, MYF(0), key_usage_type_name[type], + "USE INDEX"); + return TRUE; + } + + if (type == INDEX_HINT_USE) + has_use_index= TRUE; + return FALSE; +} + + +/* + Returns the current head of the index hints tagged list. + + SYNOPSIS + current_index_hint() + + DESCRIPTION + Used in index hint validity checks. + + RETURN VALUE + Returns the last hint added to the hints tagged list +*/ +key_usage * st_select_lex::current_index_hint() +{ + if (index_hints && index_hints->elements) + return index_hints->head(); + return NULL; +} + +/* + Check the logical correctness of a index hint clause + + SYNOPSIS + validate_index_hint() + old_top The top of the index hints list before the current clause. + + DESCRIPTION + Checks validity constraints about the table's index hints and throws + an error if an invalid combination is found. + It detects erroneous constructs like: + IGNORE INDEX (i1,...) USE INDEX () + IGNORE INDEX (i1,...) FORCE INDEX () + + RETURN VALUE + FALSE no errors found + TRUE found and reported an error. +*/ +bool st_select_lex::validate_index_hint(key_usage *old_top) +{ + key_usage *current_top= current_index_hint(); + switch (key_usage_type) + { + case INDEX_HINT_USE: + case INDEX_HINT_FORCE: + if (current_top && old_top == current_top) + { + my_error (ER_WRONG_USAGE, MYF(0), key_usage_type_name[key_usage_type], + key_usage_type_name[old_top->type]); + return TRUE; + } + default: + return FALSE; + } +} --- 1.252/sql/sql_lex.h 2006-12-05 19:52:54 +02:00 +++ 1.253/sql/sql_lex.h 2007-01-04 17:19:38 +02:00 @@ -208,6 +208,33 @@ enum tablespace_op_type NO_TABLESPACE_OP, DISCARD_TABLESPACE, IMPORT_TABLESPACE }; +enum key_usage_type +{ + INDEX_HINT_IGNORE_JOIN, + INDEX_HINT_IGNORE_GROUP, + INDEX_HINT_IGNORE_ORDER, + INDEX_HINT_IGNORE, + INDEX_HINT_USE, + INDEX_HINT_FORCE +}; + +/* keep in sync with key_usage_type */ +extern const char * key_usage_type_name[]; + +class key_usage : public Sql_alloc +{ +public: + enum key_usage_type type; + LEX_STRING name; + + key_usage (enum key_usage_type type_arg, char *str, uint length) : + type(type_arg) + { + name.str= str; + name.length= length; + } +}; + /* The state of the lex parsing for selects @@ -386,15 +413,12 @@ public: virtual uint get_in_sum_expr(); virtual TABLE_LIST* get_table_list(); virtual List<Item>* get_item_list(); - virtual List<String>* get_use_index(); - virtual List<String>* get_ignore_index(); virtual ulong get_table_join_options(); virtual TABLE_LIST *add_table_to_list(THD *thd, Table_ident *table, LEX_STRING *alias, ulong table_options, thr_lock_type flags= TL_UNLOCK, - List<String> *use_index= 0, - List<String> *ignore_index= 0, + List<key_usage> *hints= 0, LEX_STRING *option= 0); virtual void set_lock_for_tables(thr_lock_type lock_type) {} @@ -508,6 +532,14 @@ typedef class st_select_lex_unit SELECT_ */ class st_select_lex: public st_select_lex_node { +private: + /* current index hint kind. used in filling up index_hints */ + enum key_usage_type key_usage_type; + /* a list of USE/FORCE/IGNORE INDEX */ + List<key_usage> *index_hints; + /* true if there was an USE INDEX clause */ + bool has_use_index; + public: Name_resolution_context context; char *db; @@ -521,8 +553,7 @@ public: SQL_LIST table_list; SQL_LIST group_list; /* GROUP BY clause. */ List<Item> item_list; /* list of fields & expressions */ - List<String> interval_list, use_index, *use_index_ptr, - ignore_index, *ignore_index_ptr; + List<String> interval_list; bool is_item_list_lookup; /* Usualy it is pointer to ftfunc_list_alloc, but in union used to create fake @@ -645,8 +676,7 @@ public: LEX_STRING *alias, ulong table_options, thr_lock_type flags= TL_UNLOCK, - List<String> *use_index= 0, - List<String> *ignore_index= 0, + List<key_usage> *hints= 0, LEX_STRING *option= 0); TABLE_LIST* get_table_list(); bool init_nested_join(THD *thd); @@ -655,8 +685,6 @@ public: void add_joined_table(TABLE_LIST *table); TABLE_LIST *convert_right_join(); List<Item>* get_item_list(); - List<String>* get_use_index(); - List<String>* get_ignore_index(); ulong get_table_join_options(); void set_lock_for_tables(thr_lock_type lock_type); inline void init_order() @@ -696,6 +724,36 @@ public: select lexes. */ void cleanup_all_joins(bool full); + + /* add a index hint to the tagged list of hints */ + bool add_index_hint (char *str, uint length) + { + alloc_index_hints(); + return index_hints->push_front (new key_usage(key_usage_type, + str, length)); + } + + bool set_index_hint_type(enum key_usage_type type); + key_usage *current_index_hint(); + bool validate_index_hint(key_usage *old_top); + + /* make a list to hold index hints */ + void alloc_index_hints () + { + if (!index_hints) + index_hints= new List<key_usage>(); + } + + /* read and clear the index hints */ + List<key_usage>* get_index_hints(void) + { + List<key_usage> *hints= index_hints; + index_hints= NULL; + return hints; + } + + void clear_index_hints(void) { index_hints= NULL; has_use_index= FALSE; } + }; typedef class st_select_lex SELECT_LEX; --- 1.611/sql/sql_parse.cc 2006-12-07 00:44:51 +02:00 +++ 1.612/sql/sql_parse.cc 2007-01-04 17:19:39 +02:00 @@ -2382,8 +2382,7 @@ int prepare_schema_table(THD *thd, LEX * /* 'parent_lex' is used in init_query() so it must be before it. */ sel->parent_lex= lex; sel->init_query(); - if (!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ, - (List<String> *) 0, (List<String> *) 0)) + if (!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ)) DBUG_RETURN(1); lex->query_tables_last= query_tables_last; TABLE_LIST *table_list= (TABLE_LIST*) sel->table_list.first; @@ -6331,14 +6330,12 @@ bool add_to_list(THD *thd, SQL_LIST &lis # Pointer to TABLE_LIST element added to the total table list */ -TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, - Table_ident *table, - LEX_STRING *alias, - ulong table_options, - thr_lock_type lock_type, - List<String> *use_index_arg, - List<String> *ignore_index_arg, - LEX_STRING *option) +TABLE_LIST *st_select_lex::add_table_to_list +( + THD *thd, Table_ident *table, LEX_STRING *alias, + ulong table_options, thr_lock_type lock_type, + List<key_usage> *index_hints_arg, LEX_STRING *option +) { register TABLE_LIST *ptr; TABLE_LIST *previous_table_ref; /* The table preceding the current one. */ @@ -6412,12 +6409,7 @@ TABLE_LIST *st_select_lex::add_table_to_ } ptr->select_lex= lex->current_select; ptr->cacheable_table= 1; - if (use_index_arg) - ptr->use_index=(List<String> *) thd->memdup((gptr) use_index_arg, - sizeof(*use_index_arg)); - if (ignore_index_arg) - ptr->ignore_index=(List<String> *) thd->memdup((gptr) ignore_index_arg, - sizeof(*ignore_index_arg)); + ptr->index_hints= index_hints_arg; ptr->option= option ? option->str : 0; /* check that used name is unique */ if (lock_type != TL_IGNORE) --- 1.473/sql/sql_select.cc 2006-11-30 22:38:09 +02:00 +++ 1.474/sql/sql_select.cc 2007-01-04 17:19:40 +02:00 @@ -165,13 +165,15 @@ static COND *make_cond_for_table(COND *c static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order, - ha_rows select_limit, bool no_changes); + ha_rows select_limit, bool no_changes, + key_map *map); static bool list_contains_unique_index(TABLE *table, bool (*find_func) (Field *, void *), void *data); static bool find_field_in_item_list (Field *field, void *data); static bool find_field_in_order_list (Field *field, void *data); static int create_sort_index(THD *thd, JOIN *join, ORDER *order, - ha_rows filesort_limit, ha_rows select_limit); + ha_rows filesort_limit, ha_rows select_limit, + bool is_order_by); static int remove_duplicates(JOIN *join,TABLE *entry,List<Item> &fields, Item *having); static int remove_dup_with_compare(THD *thd, TABLE *entry, Field **field, @@ -916,14 +918,19 @@ JOIN::optimize() JOIN_TAB *tab= &join_tab[const_tables]; bool all_order_fields_used; if (order) - skip_sort_order= test_if_skip_sort_order(tab, order, select_limit, 1); + skip_sort_order= + test_if_skip_sort_order(tab, order, select_limit, 1, + &tab->table-> + keys_in_use_for_order_by); if ((group_list=create_distinct_group(thd, select_lex->ref_pointer_array, order, fields_list, &all_order_fields_used))) { - bool skip_group= (skip_sort_order && - test_if_skip_sort_order(tab, group_list, select_limit, - 1) != 0); + bool skip_group= + (skip_sort_order && + test_if_skip_sort_order(tab, group_list, select_limit, 1, + &tab->table-> + keys_in_use_for_group_by) != 0); if ((skip_group && all_order_fields_used) || select_limit == HA_POS_ERROR || (order && !skip_sort_order)) @@ -1113,7 +1120,9 @@ JOIN::optimize() ((group_list && (!simple_group || !test_if_skip_sort_order(&join_tab[const_tables], group_list, - unit->select_limit_cnt, 0))) || + unit->select_limit_cnt, 0, + &join_tab[const_tables].table-> + keys_in_use_for_group_by))) || select_distinct) && tmp_table_param.quick_group && !procedure) { @@ -1215,7 +1224,7 @@ JOIN::optimize() DBUG_PRINT("info",("Sorting for group")); thd->proc_info="Sorting for group"; if (create_sort_index(thd, this, group_list, - HA_POS_ERROR, HA_POS_ERROR) || + HA_POS_ERROR, HA_POS_ERROR, FALSE) || alloc_group_fields(this, group_list) || make_sum_func_list(all_fields, fields_list, 1) || setup_sum_funcs(thd, sum_funcs)) @@ -1232,7 +1241,7 @@ JOIN::optimize() DBUG_PRINT("info",("Sorting for order")); thd->proc_info="Sorting for order"; if (create_sort_index(thd, this, order, - HA_POS_ERROR, HA_POS_ERROR)) + HA_POS_ERROR, HA_POS_ERROR, TRUE)) DBUG_RETURN(1); order=0; } @@ -1259,7 +1268,9 @@ JOIN::optimize() { /* Should always succeed */ if (test_if_skip_sort_order(&join_tab[const_tables], - order, unit->select_limit_cnt, 0)) + order, unit->select_limit_cnt, 0, + &join_tab[const_tables].table-> + keys_in_use_for_order_by)) order=0; } } @@ -1452,7 +1463,9 @@ JOIN::exec() (const_tables == tables || ((simple_order || skip_sort_order) && test_if_skip_sort_order(&join_tab[const_tables], order, - select_limit, 0)))) + select_limit, 0, + &join_tab[const_tables].table-> + keys_in_use_for_order_by)))) order=0; having= tmp_having; select_describe(this, need_tmp, @@ -1628,7 +1641,7 @@ JOIN::exec() DBUG_VOID_RETURN; } if (create_sort_index(thd, curr_join, curr_join->group_list, - HA_POS_ERROR, HA_POS_ERROR) || + HA_POS_ERROR, HA_POS_ERROR, FALSE) || make_group_fields(this, curr_join)) { DBUG_VOID_RETURN; @@ -1844,7 +1857,8 @@ JOIN::exec() curr_join->group_list : curr_join->order, curr_join->select_limit, (select_options & OPTION_FOUND_ROWS ? - HA_POS_ERROR : unit->select_limit_cnt))) + HA_POS_ERROR : unit->select_limit_cnt), + curr_join->group_list ? TRUE : FALSE)) DBUG_VOID_RETURN; sortorder= curr_join->sortorder; if (curr_join->const_tables != curr_join->tables && @@ -3842,7 +3856,7 @@ best_access_path(JOIN *join, /* Limit the number of matched rows */ tmp= records; set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); - if (table->used_keys.is_set(key)) + if (table->key_usable_in_read_only.is_set(key)) { /* we can use only index tree */ uint keys_per_block= table->file->stats.block_size/2/ @@ -4009,7 +4023,7 @@ best_access_path(JOIN *join, /* Limit the number of matched rows */ set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); - if (table->used_keys.is_set(key)) + if (table->key_usable_in_read_only.is_set(key)) { /* we can use only index tree */ uint keys_per_block= table->file->stats.block_size/2/ @@ -4068,7 +4082,7 @@ best_access_path(JOIN *join, !(s->quick && best_key && s->quick->index == best_key->key && // (2) best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2) !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3) - ! s->table->used_keys.is_clear_all() && best_key) && // (3) + ! s->table->key_usable_in_read_only.is_clear_all() && best_key) && // (3) !(s->table->force_index && best_key && !s->quick)) // (4) { // Check full join ha_rows rnd_records= s->found_records; @@ -5965,7 +5979,7 @@ make_join_readinfo(JOIN *join, ulonglong (table == join->sort_by_table && (!join->order || join->skip_sort_order || test_if_skip_sort_order(tab, join->order, join->select_limit, - 1)) + 1, &table->keys_in_use_for_order_by)) ) || (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)) ordered_set= 1; @@ -5982,7 +5996,7 @@ make_join_readinfo(JOIN *join, ulonglong table->status=STATUS_NO_RECORD; tab->read_first_record= join_read_const; tab->read_record.read_record= join_no_more_records; - if (table->used_keys.is_set(tab->ref.key) && + if (table->key_usable_in_read_only.is_set(tab->ref.key) && !table->no_keyread) { table->key_read=1; @@ -6000,7 +6014,7 @@ make_join_readinfo(JOIN *join, ulonglong tab->quick=0; tab->read_first_record= join_read_key; tab->read_record.read_record= join_no_more_records; - if (table->used_keys.is_set(tab->ref.key) && + if (table->key_usable_in_read_only.is_set(tab->ref.key) && !table->no_keyread) { table->key_read=1; @@ -6017,7 +6031,7 @@ make_join_readinfo(JOIN *join, ulonglong } delete tab->quick; tab->quick=0; - if (table->used_keys.is_set(tab->ref.key) && + if (table->key_usable_in_read_only.is_set(tab->ref.key) && !table->no_keyread) { table->key_read=1; @@ -6103,15 +6117,15 @@ make_join_readinfo(JOIN *join, ulonglong { if (tab->select && tab->select->quick && tab->select->quick->index != MAX_KEY && //not index_merge - table->used_keys.is_set(tab->select->quick->index)) + table->key_usable_in_read_only.is_set(tab->select->quick->index)) { table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else if (!table->used_keys.is_clear_all() && + else if (!table->key_usable_in_read_only.is_clear_all() && !(tab->select && tab->select->quick)) { // Only read index tree - tab->index=find_shortest_key(table, & table->used_keys); + tab->index=find_shortest_key(table, & table->key_usable_in_read_only); tab->read_first_record= join_read_first; tab->type=JT_NEXT; // Read with index_first / index_next } @@ -9179,7 +9193,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA table->copy_blobs= 1; table->in_use= thd; table->quick_keys.init(); - table->used_keys.init(); + table->key_usable_in_read_only.init(); table->keys_in_use_for_query.init(); table->s= share; @@ -10804,7 +10818,7 @@ join_read_const_table(JOIN_TAB *tab, POS } else { - if (!table->key_read && table->used_keys.is_set(tab->ref.key) && + if (!table->key_read && table->key_usable_in_read_only.is_set(tab->ref.key) && !table->no_keyread && (int) table->reginfo.lock_type <= (int) TL_READ_HIGH_PRIORITY) { @@ -11109,7 +11123,7 @@ join_read_first(JOIN_TAB *tab) { int error; TABLE *table=tab->table; - if (!table->key_read && table->used_keys.is_set(tab->index) && + if (!table->key_read && table->key_usable_in_read_only.is_set(tab->index) && !table->no_keyread) { table->key_read=1; @@ -11148,7 +11162,7 @@ join_read_last(JOIN_TAB *tab) { TABLE *table=tab->table; int error; - if (!table->key_read && table->used_keys.is_set(tab->index) && + if (!table->key_read && table->key_usable_in_read_only.is_set(tab->index) && !table->no_keyread) { table->key_read=1; @@ -12170,7 +12184,7 @@ find_field_in_item_list (Field *field, v static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, - bool no_changes) + bool no_changes, key_map *map) { int ref_key; uint ref_key_parts; @@ -12184,9 +12198,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR Check which keys can be used to resolve ORDER BY. We must not try to use disabled keys. */ - usable_keys= table->s->keys_in_use; - /* we must not consider keys that are disabled by IGNORE INDEX */ - usable_keys.intersect(table->keys_in_use_for_query); + usable_keys= *map; for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next) { @@ -12244,8 +12256,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR If using index only read, only consider other possible index only keys */ - if (table->used_keys.is_set(ref_key)) - usable_keys.intersect(table->used_keys); + if (table->key_usable_in_read_only.is_set(ref_key)) + usable_keys.intersect(table->key_usable_in_read_only); if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts, &usable_keys)) < MAX_KEY) { @@ -12360,7 +12372,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR if (select_limit >= table->file->stats.records) { keys= *table->file->keys_to_use_for_scanning(); - keys.merge(table->used_keys); + keys.merge(table->key_usable_in_read_only); /* We are adding here also the index specified in FORCE INDEX clause, @@ -12388,7 +12400,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR tab->read_first_record= (flag > 0 ? join_read_first: join_read_last); tab->type=JT_NEXT; // Read with index_first(), index_next() - if (table->used_keys.is_set(nr)) + if (table->key_usable_in_read_only.is_set(nr)) { table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); @@ -12432,7 +12444,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR static int create_sort_index(THD *thd, JOIN *join, ORDER *order, - ha_rows filesort_limit, ha_rows select_limit) + ha_rows filesort_limit, ha_rows select_limit, + bool is_order_by) { uint length; ha_rows examined_rows; @@ -12453,7 +12466,9 @@ create_sort_index(THD *thd, JOIN *join, */ if ((order != join->group_list || !(join->select_options & SELECT_BIG_RESULT)) && - test_if_skip_sort_order(tab,order,select_limit,0)) + 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)) DBUG_RETURN(0); if (!(join->sortorder= make_unireg_sortorder(order,&length,join->sortorder))) @@ -15047,7 +15062,7 @@ static void select_describe(JOIN *join, /* Build "Extra" field and add it to item_list. */ my_bool key_read=table->key_read; if ((tab->type == JT_NEXT || tab->type == JT_CONST) && - table->used_keys.is_set(tab->index)) + table->key_usable_in_read_only.is_set(tab->index)) key_read=1; if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row) --- 1.379/sql/sql_show.cc 2006-12-01 22:12:01 +02:00 +++ 1.380/sql/sql_show.cc 2007-01-04 17:19:41 +02:00 @@ -2269,8 +2269,7 @@ int make_table_list(THD *thd, SELECT_LEX ident_table.length= strlen(table); table_ident= new Table_ident(thd, ident_db, ident_table, 1); sel->init_query(); - if (!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ, - (List<String> *) 0, (List<String> *) 0)) + if (!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ)) return 1; return 0; } @@ -4934,8 +4933,7 @@ int make_schema_select(THD *thd, SELECT_ strlen(schema_table->table_name), 0); if (schema_table->old_format(thd, schema_table) || /* Handle old syntax */ !sel->add_table_to_list(thd, new Table_ident(thd, db, table, 0), - 0, 0, TL_READ, (List<String> *) 0, - (List<String> *) 0)) + 0, 0, TL_READ)) { DBUG_RETURN(1); } --- 1.215/sql/sql_update.cc 2006-11-30 22:38:09 +02:00 +++ 1.216/sql/sql_update.cc 2007-01-04 17:19:42 +02:00 @@ -130,7 +130,7 @@ int mysql_update(THD *thd, #endif uint table_count= 0; ha_rows updated, found; - key_map old_used_keys; + key_map old_key_usable_in_read_only; TABLE *table; SQL_SELECT *select; READ_RECORD info; @@ -168,8 +168,8 @@ int mysql_update(THD *thd, thd->proc_info="init"; table= table_list->table; - /* Calculate "table->used_keys" based on the WHERE */ - table->used_keys= table->s->keys_in_use; + /* Calculate "table->key_usable_in_read_only" based on the WHERE */ + table->key_usable_in_read_only= table->s->keys_in_use; table->quick_keys.clear_all(); #ifndef NO_EMBEDDED_ACCESS_CHECKS @@ -179,7 +179,7 @@ int mysql_update(THD *thd, if (mysql_prepare_update(thd, table_list, &conds, order_num, order)) DBUG_RETURN(1); - old_used_keys= table->used_keys; // Keys used in WHERE + old_key_usable_in_read_only= table->key_usable_in_read_only; // Keys used in WHERE /* Check the fields we are going to modify */ #ifndef NO_EMBEDDED_ACCESS_CHECKS table_list->grant.want_privilege= table->grant.want_privilege= want_privilege; @@ -228,7 +228,7 @@ int mysql_update(THD *thd, limit= 0; // Impossible WHERE } // Don't count on usage of 'only index' when calculating which key to use - table->used_keys.clear_all(); + table->key_usable_in_read_only.clear_all(); #ifdef WITH_PARTITION_STORAGE_ENGINE if (prune_partitions(thd, table, conds)) @@ -303,7 +303,7 @@ int mysql_update(THD *thd, We can't update table directly; We must first search after all matching rows before updating the table! */ - if (used_index < MAX_KEY && old_used_keys.is_set(used_index)) + if (used_index < MAX_KEY && old_key_usable_in_read_only.is_set(used_index)) { table->key_read=1; table->mark_columns_used_by_index(used_index); @@ -1091,7 +1091,7 @@ int multi_update::prepare(List<Item> &no } /* - We have to check values after setup_tables to get used_keys right in + We have to check values after setup_tables to get key_usable_in_read_only right in reference tables */ @@ -1118,7 +1118,7 @@ int multi_update::prepare(List<Item> &no update.link_in_list((byte*) tl, (byte**) &tl->next_local); tl->shared= table_count++; table->no_keyread=1; - table->used_keys.clear_all(); + table->key_usable_in_read_only.clear_all(); table->pos_in_table_list= tl; } } --- 1.524/sql/sql_yacc.yy 2006-12-07 14:57:27 +02:00 +++ 1.525/sql/sql_yacc.yy 2007-01-04 17:19:43 +02:00 @@ -150,6 +150,7 @@ static bool is_native_function(THD *thd, struct st_lex *lex; sp_head *sphead; struct p_elem_val *p_elem_value; + key_usage *key_usage_ptr; } %{ @@ -820,7 +821,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** btree_or_rtree %type <string_list> - key_usage_list using_list + using_list %type <key_part> key_part @@ -890,7 +891,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** opt_column_list grant_privileges grant_ident grant_list grant_option object_privilege object_privilege_list user_list rename_list clear_privileges flush_options flush_option - equal optional_braces opt_key_definition key_usage_list2 + equal optional_braces key_usage_list2 opt_mi_check_type opt_to mi_check_types normal_join db_to_db table_to_table_list table_to_table opt_table_list opt_as handler_rkey_function handler_read_or_scan @@ -5527,8 +5528,7 @@ assign_to_keycache: SELECT_LEX *sel= &lex->select_lex; if (!sel->add_table_to_list(lex->thd, $1, NULL, 0, TL_READ, - sel->get_use_index(), - (List<String> *)0)) + sel->get_index_hints())) YYABORT; } ; @@ -5559,29 +5559,23 @@ preload_keys: SELECT_LEX *sel= &lex->select_lex; if (!sel->add_table_to_list(lex->thd, $1, NULL, $3, TL_READ, - sel->get_use_index(), - (List<String> *)0)) + sel->get_index_hints())) YYABORT; } ; cache_keys_spec: - { Select->interval_list.empty(); } - cache_key_list_or_empty - { - LEX *lex=Lex; - SELECT_LEX *sel= &lex->select_lex; - sel->use_index= sel->interval_list; + { + Lex->select_lex.clear_index_hints(); + if (Select->set_index_hint_type(INDEX_HINT_USE)) + YYABORT; } + cache_key_list_or_empty ; cache_key_list_or_empty: - /* empty */ { Lex->select_lex.use_index_ptr= 0; } - | opt_key_or_index '(' key_usage_list2 ')' - { - SELECT_LEX *sel= &Lex->select_lex; - sel->use_index_ptr= &sel->use_index; - } + /* empty */ { } + | key_usage_list ; opt_ignore_leaves: @@ -6949,7 +6943,6 @@ normal_join: table_factor: { SELECT_LEX *sel= Select; - sel->use_index_ptr=sel->ignore_index_ptr=0; sel->table_join_options= 0; } table_ident opt_table_alias opt_key_definition @@ -6959,8 +6952,7 @@ table_factor: if (!($$= sel->add_table_to_list(lex->thd, $2, $3, sel->get_table_join_options(), lex->lock_option, - sel->get_use_index(), - sel->get_ignore_index()))) + sel->get_index_hints()))) YYABORT; sel->add_joined_table($$); } @@ -7031,8 +7023,7 @@ table_factor: lex->current_select= sel= unit->outer_select(); if (!($$= sel-> add_table_to_list(lex->thd, new Table_ident(unit), $6, 0, - TL_READ,(List<String> *)0, - (List<String> *)0))) + TL_READ))) YYABORT; sel->add_joined_table($$); @@ -7131,32 +7122,71 @@ opt_outer: /* empty */ {} | OUTER {}; +opt_key_definition_clause: + USE_SYM + { + if (Select->set_index_hint_type(INDEX_HINT_USE)) + YYABORT; + /* must allocate a hints array to mark empty USE INDEX clause */ + Select->alloc_index_hints(); + $<key_usage_ptr>$= Select->current_index_hint(); + } key_usage_list + { + if (Select->validate_index_hint($<key_usage_ptr>2)) + YYABORT; + } + | FORCE_SYM + { + if (Select->set_index_hint_type(INDEX_HINT_FORCE)) + YYABORT; + $<key_usage_ptr>$= Select->current_index_hint(); + } key_usage_list + { + if (Select->validate_index_hint($<key_usage_ptr>2)) + YYABORT; + Select->table_join_options|= TL_OPTION_FORCE_INDEX; + } + | IGNORE_SYM key_or_index '(' + { + if (Select->set_index_hint_type (INDEX_HINT_IGNORE)) + YYABORT; + } + key_list_or_empty ')' + | IGNORE_SYM key_or_index FOR_SYM JOIN_SYM '(' + { + if (Select->set_index_hint_type(INDEX_HINT_IGNORE_JOIN)) + YYABORT; + } + key_list_or_empty ')' + + | IGNORE_SYM key_or_index FOR_SYM ORDER_SYM BY '(' + { + if (Select->set_index_hint_type(INDEX_HINT_IGNORE_ORDER)) + YYABORT; + } + key_list_or_empty ')' + + | IGNORE_SYM key_or_index FOR_SYM GROUP BY '(' + { + if (Select->set_index_hint_type(INDEX_HINT_IGNORE_GROUP)) + YYABORT; + } + key_list_or_empty ')' + ; + +opt_key_definition2: + /* empty */ {} + | opt_key_definition2 opt_key_definition_clause + ; + opt_key_definition: - /* empty */ {} - | USE_SYM key_usage_list - { - SELECT_LEX *sel= Select; - sel->use_index= *$2; - sel->use_index_ptr= &sel->use_index; - } - | FORCE_SYM key_usage_list - { - SELECT_LEX *sel= Select; - sel->use_index= *$2; - sel->use_index_ptr= &sel->use_index; - sel->table_join_options|= TL_OPTION_FORCE_INDEX; - } - | IGNORE_SYM key_usage_list - { - SELECT_LEX *sel= Select; - sel->ignore_index= *$2; - sel->ignore_index_ptr= &sel->ignore_index; - }; + { Select->clear_index_hints(); } + opt_key_definition2 + ; key_usage_list: - key_or_index { Select->interval_list.empty(); } + key_or_index '(' key_list_or_empty ')' - { $$= &Select->interval_list; } ; key_list_or_empty: @@ -7166,17 +7196,11 @@ key_list_or_empty: key_usage_list2: key_usage_list2 ',' ident - { Select-> - interval_list.push_back(new (YYTHD->mem_root) String((const char*) $3.str, $3.length, - system_charset_info)); } + { Select->add_index_hint ($3.str, $3.length); } | ident - { Select-> - interval_list.push_back(new (YYTHD->mem_root) String((const char*) $1.str, $1.length, - system_charset_info)); } + { Select->add_index_hint ($1.str, $1.length); } | PRIMARY_SYM - { Select-> - interval_list.push_back(new (YYTHD->mem_root) String("PRIMARY", 7, - system_charset_info)); }; + { Select->add_index_hint ((char *)"PRIMARY", 7); }; using_list: ident --- 1.262/sql/table.cc 2006-12-03 18:02:54 +02:00 +++ 1.263/sql/table.cc 2007-01-04 17:19:44 +02:00 @@ -1354,7 +1354,7 @@ int open_table_from_share(THD *thd, TABL if (!(outparam->alias= my_strdup(alias, MYF(MY_WME)))) goto err; outparam->quick_keys.init(); - outparam->used_keys.init(); + outparam->key_usable_in_read_only.init(); outparam->keys_in_use_for_query.init(); /* Allocate handler */ @@ -4131,6 +4131,91 @@ void st_table_list::reinit_before_use(TH Item_subselect *st_table_list::containing_subselect() { return (select_lex ? select_lex->master_unit()->item : 0); +} + +/* + Compiles the tagged hints list and fills up the bitmasks. + + SYNOPSIS + process_index_hints() + table the TABLE to operate on. + + DESCRIPTION + Resolves the index names and fills up the index masks + by processing the index hints tagged list that is + filled by the parser. + + RETURN VALUE + FALSE no errors found + TRUE found and reported an error. +*/ +bool st_table_list::process_index_hints(TABLE *table) +{ + if (index_hints) + { + key_map index_use, index_ignore_join, index_ignore_group, + index_ignore_order; + key_usage *hint; + List_iterator <key_usage> iter(*index_hints); + + index_use.clear_all(); + index_ignore_join.clear_all(); + index_ignore_group.clear_all(); + index_ignore_order.clear_all(); + while ((hint= iter++)) + { + uint pos; + + if (table->s->keynames.type_names == 0 || + (pos= find_type(&table->s->keynames, hint->name.str, + hint->name.length, 1)) <= 0) + { + my_error(ER_KEY_DOES_NOT_EXITS, MYF(0), hint->name.str, alias); + return 1; + } + switch (hint->type) + { + case INDEX_HINT_USE: + case INDEX_HINT_FORCE: + index_use.set_bit(pos-1); break; + + case INDEX_HINT_IGNORE: + index_ignore_join.set_bit(pos-1); + if (!global_system_variables.opt_old) + { + index_ignore_order.set_bit(pos-1); + index_ignore_group.set_bit(pos-1); + } + break; + case INDEX_HINT_IGNORE_JOIN: + index_ignore_join.set_bit(pos-1); break; + case INDEX_HINT_IGNORE_GROUP: + index_ignore_group.set_bit(pos-1); break; + case INDEX_HINT_IGNORE_ORDER: + index_ignore_order.set_bit(pos-1); break; + } + } + + if (!index_use.is_clear_all() || + /* USE INDEX () empty list */ + (index_ignore_join.is_clear_all() && + index_ignore_order.is_clear_all() && + index_ignore_group.is_clear_all())) + { + table->keys_in_use_for_query= table->keys_in_use_for_group_by= + table->keys_in_use_for_order_by= index_use; + } + + if (!index_ignore_join.is_clear_all()) + table->keys_in_use_for_query.subtract(index_ignore_join); + if (!index_ignore_order.is_clear_all()) + table->keys_in_use_for_order_by.subtract(index_ignore_order); + if (!index_ignore_group.is_clear_all()) + table->keys_in_use_for_group_by.subtract(index_ignore_group); + } + + table->key_usable_in_read_only.intersect(table->keys_in_use_for_query); + return 0; } /***************************************************************************** --- 1.155/sql/table.h 2006-11-29 22:54:07 +02:00 +++ 1.156/sql/table.h 2007-01-04 17:19:44 +02:00 @@ -314,7 +314,8 @@ struct st_table { byte *write_row_record; /* Used as optimisation in THD::write_row */ byte *insert_values; /* used by INSERT ... UPDATE */ - key_map quick_keys, used_keys, keys_in_use_for_query, merge_keys; + key_map quick_keys, key_usable_in_read_only, keys_in_use_for_query, merge_keys, + keys_in_use_for_group_by, keys_in_use_for_order_by; KEY *key_info; /* data of keys in database */ Field *next_number_field; /* Set if next_number is activated */ @@ -636,6 +637,7 @@ public: (TABLE_LIST::join_using_fields != NULL) */ +class key_usage; typedef struct st_table_list { st_table_list() {} /* Remove gcc warning */ @@ -692,7 +694,7 @@ typedef struct st_table_list */ struct st_table_list *next_name_resolution_table; /* Index names in a "... JOIN ... USE/IGNORE INDEX ..." clause. */ - List<String> *use_index, *ignore_index; + List<key_usage> *index_hints; TABLE *table; /* opened table */ uint table_id; /* table id (from binlog) for opened table */ /* @@ -865,6 +867,8 @@ typedef struct st_table_list */ void reinit_before_use(THD *thd); Item_subselect *containing_subselect(); + + bool process_index_hints(TABLE *table); private: bool prep_check_option(THD *thd, uint8 check_opt_type); --- 1.76/mysql-test/r/group_by.result 2006-10-19 17:07:02 +03:00 +++ 1.77/mysql-test/r/group_by.result 2007-01-04 17:19:30 +02:00 @@ -933,12 +933,64 @@ b sum(1) 18 6 19 6 DROP TABLE t1; -CREATE TABLE t1 (a INT, b INT, KEY(a)); -INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); -EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; +CREATE TABLE t1 (a INT, b INT, +PRIMARY KEY (a), +KEY i2(a,b)); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); +INSERT INTO t1 SELECT a + 8,b FROM t1; +INSERT INTO t1 SELECT a + 16,b FROM t1; +INSERT INTO t1 SELECT a + 32,b FROM t1; +INSERT INTO t1 SELECT a + 64,b FROM t1; +INSERT INTO t1 SELECT a + 128,b FROM t1; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT a FROM t1 WHERE a < 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 5 NULL 4 -EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2; +1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 2 Using where; Using index +EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort -DROP TABLE t1; +1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 2 Using where; Using index +EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,i2 PRIMARY 4 NULL 2 Using where; Using index +EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 256 Using index +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 256 Using index; Using filesort +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) +IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 256 Using index +EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +EXPLAIN SELECT a FROM t1 FORCE INDEX (i2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL i2 9 NULL 256 Using index +EXPLAIN SELECT a FROM t1 USE INDEX (); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 256 +EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2); +ERROR HY000: Incorrect usage of USE INDEX and USE INDEX +EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); +ERROR HY000: Incorrect usage of IGNORE INDEX and USE INDEX +EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX (); +ERROR HY000: Incorrect usage of USE INDEX and IGNORE INDEX +CREATE TABLE t2 (a INT, b INT, KEY(a)); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); +EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL a 5 NULL 4 +EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +DROP TABLE t1, t2; --- 1.61/mysql-test/t/group_by.test 2006-10-19 17:16:35 +03:00 +++ 1.62/mysql-test/t/group_by.test 2007-01-04 17:19:31 +02:00 @@ -706,10 +706,37 @@ DROP TABLE t1; # Bug #21174: Index degrades sort performance and # optimizer does not honor IGNORE INDEX # -CREATE TABLE t1 (a INT, b INT, KEY(a)); -INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); +CREATE TABLE t1 (a INT, b INT, + PRIMARY KEY (a), + KEY i2(a,b)); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); +INSERT INTO t1 SELECT a + 8,b FROM t1; +INSERT INTO t1 SELECT a + 16,b FROM t1; +INSERT INTO t1 SELECT a + 32,b FROM t1; +INSERT INTO t1 SELECT a + 64,b FROM t1; +INSERT INTO t1 SELECT a + 128,b FROM t1; +ANALYZE TABLE t1; +EXPLAIN SELECT a FROM t1 WHERE a < 2; +EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a; +EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a; +EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2); +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2); +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) + IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; +EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2); +EXPLAIN SELECT a FROM t1 FORCE INDEX (i2); +EXPLAIN SELECT a FROM t1 USE INDEX (); +--error ER_WRONG_USAGE +EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2); +--error ER_WRONG_USAGE +EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); +--error ER_WRONG_USAGE +EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX (); -EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; -EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2; - -DROP TABLE t1; +CREATE TABLE t2 (a INT, b INT, KEY(a)); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); +EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; +EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; +DROP TABLE t1, t2; --- 1.53/sql/sql_help.cc 2006-06-04 18:52:14 +03:00 +++ 1.54/sql/sql_help.cc 2007-01-04 17:19:37 +02:00 @@ -568,7 +568,7 @@ SQL_SELECT *prepare_simple_select(THD *t cond->fix_fields(thd, &cond); // can never fail /* Assume that no indexes cover all required fields */ - table->used_keys.clear_all(); + table->key_usable_in_read_only.clear_all(); SQL_SELECT *res= make_select(table, 0, 0, cond, 0, error); if (*error || (res && res->check_quick(thd, 0, HA_POS_ERROR)) ||
| Thread | ||
|---|---|---|
| • bk commit into 5.1 tree (gkodinov:1.2359) | kgeorge | 4 Jan |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.983 sec. using MySQL 5.1.14-beta-log