From 79542930ea1c969a9300fe622be15eeecee2c48e Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Mar 2007 19:08:41 +0200 Subject: 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 USE/FORCE/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/used 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/USE/FORCE INDEX syntax to: IGNORE/USE/FORCE INDEX [FOR {JOIN | ORDER | GROUP BY}] so that: - if no FOR is specified, the index hint will apply everywhere. - if MySQL is started with the compatibility option --old_mode then an index hint 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: Rename: sql/mysqld.cc.rej -> BitKeeper/deleted/.del-mysqld.cc.rej BitKeeper/deleted/.del-sql_parse.cc.rej: Rename: sql/sql_parse.cc.rej -> BitKeeper/deleted/.del-sql_parse.cc.rej BitKeeper/deleted/.del-table.cc.rej: Rename: sql/table.cc.rej -> BitKeeper/deleted/.del-table.cc.rej mysql-test/r/endspace.result: WL3527 : fixed undeterministic test mysql-test/r/group_by.result: WL#3527: test cases mysql-test/t/endspace.test: WL3527 : fixed undeterministic test mysql-test/t/group_by.test: WL#3527: test cases sql/item.cc: WL#3527: renames sql/mysql_priv.h: WL#3527: corrected initialization sql/mysqld.cc: WL#3527: added old_mode command line option sql/opt_range.cc: WL#3527: renames sql/sql_base.cc: WL#3527: - renames - correct initialization - extended the processing of USE/FORCE/IGNORE index sql/sql_class.h: WL#3527: added old_mode command line option sql/sql_delete.cc: WL#3527: renames sql/sql_help.cc: WL#3527: renames sql/sql_lex.cc: WL#3527: extended parsing of USE/FORCE/IGNORE index sql/sql_lex.h: WL#3527: extended parsing of USE/FORCE/IGNORE index sql/sql_parse.cc: WL#3527: extended parsing of USE/FORCE/IGNORE index sql/sql_select.cc: WL#3527: - renames - passing additional info to support the extended USE/FORCE/IGNORE INDEX syntax - If there is a covering index, and we have IGNORE INDEX FOR GROUP/ORDER, and this index is used for the JOIN part, then we have to ignore the IGNORE INDEX FOR GROUP/ORDER. sql/sql_show.cc: WL#3527: passing additional info to support the extended USE/FORCE/IGNORE INDEX syntax sql/sql_update.cc: WL#3527: renames sql/sql_yacc.yy: WL#3527: extended parsing of USE/FORCE/IGNORE index sql/table.cc: WL#3527: extended the processing of USE/FORCE/IGNORE index sql/table.h: WL#3527: extended the processing of USE/FORCE/IGNORE index storage/myisam/ha_myisam.cc: WL#3527: extended the processing of USE/FORCE/IGNORE index --- storage/myisam/ha_myisam.cc | 49 ++++++++++++++++++++++----------------------- 1 file changed, 24 insertions(+), 25 deletions(-) (limited to 'storage') diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index f407f62fa0c..906d4e8a1a5 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -804,23 +804,22 @@ int ha_myisam::assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt) KEY_CACHE *new_key_cache= check_opt->key_cache; const char *errmsg= 0; int error= HA_ADMIN_OK; - ulonglong map= ~(ulonglong) 0; + ulonglong map; 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(); + + 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; } + map= ~(ulonglong) 0; + if (!table->keys_in_use_for_query.is_clear_all()) + /* use all keys if there's no list specified by the user through hints */ + map= table->keys_in_use_for_query.to_ulonglong(); if ((error= mi_assign_to_key_cache(file, map, new_key_cache))) { @@ -856,27 +855,27 @@ int ha_myisam::preload_keys(THD* thd, HA_CHECK_OPT *check_opt) { int error; const char *errmsg; - ulonglong map= ~(ulonglong) 0; + ulonglong map; TABLE_LIST *table_list= table->pos_in_table_list; my_bool ignore_leaves= table_list->ignore_leaves; 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(); + + 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; } + map= ~(ulonglong) 0; + /* Check validity of the index references */ + if (!table->keys_in_use_for_query.is_clear_all()) + /* use all keys if there's no list specified by the user through hints */ + map= table->keys_in_use_for_query.to_ulonglong(); + mi_extra(file, HA_EXTRA_PRELOAD_BUFFER_SIZE, (void *) &thd->variables.preload_buff_size); -- cgit v1.2.1