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-03-06 10:21:57+02:00, gkodinov@stripped +22 -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 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.
mysql-test/r/endspace.result@stripped, 2007-03-06 10:21:54+02:00, gkodinov@stripped +9 -6
WL3527 : fixed undeterministic test
mysql-test/r/group_by.result@stripped, 2007-03-06 10:21:54+02:00, gkodinov@stripped +105 -7
WL#3527: test cases
mysql-test/t/endspace.test@stripped, 2007-03-06 10:21:54+02:00, gkodinov@stripped +6 -3
WL3527 : fixed undeterministic test
mysql-test/t/group_by.test@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +61 -5
WL#3527: test cases
sql/item.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +1 -1
WL#3527: renames
sql/mysql_priv.h@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +2 -1
WL#3527: corrected initialization
sql/mysqld.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +6 -1
WL#3527: added old_mode command line option
sql/opt_range.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +4 -4
WL#3527: renames
sql/sql_base.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +7 -30
WL#3527:
- renames
- correct initialization
- extended the processing of USE/FORCE/IGNORE index
sql/sql_class.h@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +6 -0
WL#3527: added old_mode command line option
sql/sql_delete.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +2 -2
WL#3527: renames
sql/sql_help.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +1 -1
WL#3527: renames
sql/sql_lex.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +71 -20
WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/sql_lex.h@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +71 -10
WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/sql_parse.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +3 -10
WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/sql_select.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +59 -37
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@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +2 -4
WL#3527: passing additional info to support the extended
USE/FORCE/IGNORE INDEX syntax
sql/sql_update.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +8 -8
WL#3527: renames
sql/sql_yacc.yy@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +80 -78
WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/table.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +170 -1
WL#3527: extended the processing of USE/FORCE/IGNORE index
sql/table.h@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +27 -2
WL#3527: extended the processing of USE/FORCE/IGNORE index
storage/myisam/ha_myisam.cc@stripped, 2007-03-06 10:21:55+02:00, gkodinov@stripped +24 -25
WL#3527: extended the processing of USE/FORCE/IGNORE index
# 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: magare.gmz
# Root: /home/kgeorge/mysql/autopush/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-03-06 10:21:55 +02:00
@@ -804,23 +804,22 @@
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,26 +855,26 @@
{
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);
--- 1.225/sql/item.cc 2006-12-04 21:02:31 +02:00
+++ 1.226/sql/item.cc 2007-03-06 10:21:55 +02:00
@@ -3828,7 +3828,7 @@
{
/* First usage of column */
table->used_fields++; // Used to optimize loops
- table->used_keys.intersect(field->part_of_key);
+ table->covering_keys.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-03-06 10:21:55 +02:00
@@ -1957,7 +1957,6 @@
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)
@@ -1968,6 +1967,8 @@
table->tablenr= tablenr;
table->map= (table_map) 1 << tablenr;
table->force_index= table_list->force_index;
+ table->covering_keys= table->s->keys_for_keyread;
+ table->merge_keys.clear_all();
}
--- 1.599/sql/mysqld.cc 2006-12-05 13:11:42 +02:00
+++ 1.600/sql/mysqld.cc 2007-03-06 10:21:55 +02:00
@@ -4869,7 +4869,8 @@
OPT_PORT_OPEN_TIMEOUT,
OPT_GENERAL_LOG,
OPT_SLOW_LOG,
- OPT_MERGE
+ OPT_MERGE,
+ OPT_OLD_MODE
};
@@ -6080,6 +6081,10 @@
(gptr*) &global_system_variables.net_write_timeout,
(gptr*) &max_system_variables.net_write_timeout, 0, GET_ULONG,
REQUIRED_ARG, NET_WRITE_TIMEOUT, 1, LONG_TIMEOUT, 0, 1, 0},
+ { "old_mode", OPT_OLD_MODE, "Use compatible behaviour.",
+ (gptr*) &global_system_variables.old_mode,
+ (gptr*) &max_system_variables.old_mode, 0, GET_BOOL, NO_ARG,
+ 0, 0, 0, 0, 0, 0},
{"open_files_limit", OPT_OPEN_FILES_LIMIT,
"If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files.",
(gptr*) &open_files_limit, (gptr*) &open_files_limit, 0, GET_ULONG,
--- 1.252/sql/opt_range.cc 2006-12-02 00:11:56 +02:00
+++ 1.253/sql/opt_range.cc 2007-03-06 10:21:55 +02:00
@@ -2109,9 +2109,9 @@
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->covering_keys.is_clear_all())
{
- int key_for_use= find_shortest_key(head, &head->used_keys);
+ int key_for_use= find_shortest_key(head, &head->covering_keys);
double key_read_time= (get_index_only_read_time(¶m, records,
key_for_use) +
(double) records / TIME_FOR_COMPARE);
@@ -4646,7 +4646,7 @@
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->covering_keys.is_set(keynr);
found_records= check_quick_select(param, idx, *key, update_tbl_stats);
if (param->is_ror_scan)
@@ -8988,7 +8988,7 @@
cur_index_info++, cur_index++)
{
/* Check (B1) - if current index is covering. */
- if (!table->used_keys.is_set(cur_index))
+ if (!table->covering_keys.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-03-06 10:21:55 +02:00
@@ -1792,8 +1792,6 @@
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->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 @@
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 @@
been set for all fields (for example for view).
*/
- table->used_keys.intersect(field->part_of_key);
+ table->covering_keys.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 @@
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->covering_keys.intersect(field_1->part_of_key);
table_1->merge_keys.merge(field_1->part_of_key);
}
if (field_2)
@@ -4806,7 +4800,7 @@
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->covering_keys.intersect(field_2->part_of_key);
table_2->merge_keys.merge(field_2->part_of_key);
}
@@ -5446,25 +5440,8 @@
tablenr= 0;
}
setup_table_map(table, table_list, tablenr);
- table->used_keys= 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);
+ if (table_list->process_index_hints(table))
+ DBUG_RETURN(1);
}
if (tablenr > MAX_TABLES)
{
@@ -5746,7 +5723,7 @@
bitmap_set_bit(field->table->read_set, field->field_index);
if (table)
{
- table->used_keys.intersect(field->part_of_key);
+ table->covering_keys.intersect(field->part_of_key);
table->merge_keys.merge(field->part_of_key);
}
if (tables->is_natural_join)
@@ -5764,7 +5741,7 @@
if (field_table)
{
thd->used_tables|= field_table->map;
- field_table->used_keys.intersect(field->part_of_key);
+ field_table->covering_keys.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-03-06 10:21:55 +02:00
@@ -240,6 +240,11 @@
my_bool low_priority_updates;
my_bool new_mode;
+ /*
+ compatibility option:
+ - index usage hints (USE INDEX without a FOR clause) behave as in 5.0
+ */
+ my_bool old_mode;
my_bool query_cache_wlock_invalidate;
my_bool engine_condition_pushdown;
my_bool innodb_table_locks;
@@ -277,6 +282,7 @@
DATE_TIME_FORMAT *datetime_format;
DATE_TIME_FORMAT *time_format;
my_bool sysdate_is_now;
+
};
--- 1.204/sql/sql_delete.cc 2006-11-30 22:01:24 +02:00
+++ 1.205/sql/sql_delete.cc 2007-03-06 10:21:55 +02:00
@@ -117,7 +117,7 @@
/* Update the table->file->stats.records number */
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
- table->used_keys.clear_all();
+ table->covering_keys.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 @@
tbl->no_keyread=1;
/* Don't use record cache */
tbl->no_cache= 1;
- tbl->used_keys.clear_all();
+ tbl->covering_keys.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-03-06 10:21:55 +02:00
@@ -70,6 +70,17 @@
208,209,210,211,212,213,214,247,216,217,218,219,220,221,222,255
};
+/*
+ Names of the index hints (for error messages). Keep in sync with
+ index_hint_type
+*/
+
+const char * index_hint_type_name[] =
+{
+ "IGNORE INDEX",
+ "USE INDEX",
+ "FORCE INDEX"
+};
inline int lex_casecmp(const char *s, const char *t, uint len)
{
@@ -1173,7 +1184,6 @@
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 +1192,6 @@
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,14 +1406,11 @@
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,
+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,
+ List<index_hint> *hints,
LEX_STRING *option)
{
return 0;
@@ -1511,19 +1517,6 @@
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 +2253,61 @@
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.
+ clause the clause to use for 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 index_hint_type can be reset to the
+ next hint type.
+*/
+void st_select_lex::set_index_hint_type(enum index_hint_type type,
+ index_clause_map clause)
+{
+ current_index_hint_type= type;
+ current_index_hint_clause= clause;
+}
+
+
+/*
+ Makes an array to store index usage hints (ADD/FORCE/IGNORE INDEX).
+
+ SYNOPSIS
+ alloc_index_hints()
+ thd current thread.
+*/
+
+void st_select_lex::alloc_index_hints (THD *thd)
+{
+ index_hints= new (thd->mem_root) List<index_hint>();
+}
+
+
+
+/*
+ adds an element to the array storing index usage hints
+ (ADD/FORCE/IGNORE INDEX).
+
+ SYNOPSIS
+ add_index_hint()
+ thd current thread.
+ str name of the index.
+ length number of characters in str.
+
+ RETURN VALUE
+ 0 on success, non-zero otherwise
+*/
+bool st_select_lex::add_index_hint (THD *thd, char *str, uint length)
+{
+ return index_hints->push_front (new (thd->mem_root)
+ index_hint(current_index_hint_type,
+ current_index_hint_clause,
+ str, length));
+}
--- 1.252/sql/sql_lex.h 2006-12-05 19:52:54 +02:00
+++ 1.253/sql/sql_lex.h 2007-03-06 10:21:55 +02:00
@@ -209,6 +209,47 @@
};
/*
+ String names used to print a statement with index hints.
+ Keep in sync with index_hint_type.
+*/
+extern const char * index_hint_type_name[];
+typedef byte index_clause_map;
+
+/*
+ Bits in index_clause_map : one for each possible FOR clause in
+ USE/FORCE/IGNORE INDEX index hint specification
+*/
+#define INDEX_HINT_MASK_JOIN (1)
+#define INDEX_HINT_MASK_GROUP (1 << 1)
+#define INDEX_HINT_MASK_ORDER (1 << 2)
+
+#define INDEX_HINT_MASK_ALL (INDEX_HINT_MASK_JOIN | INDEX_HINT_MASK_GROUP | \
+ INDEX_HINT_MASK_ORDER)
+
+/* Single element of an USE/FORCE/IGNORE INDEX list specified as a SQL hint */
+class index_hint : public Sql_alloc
+{
+public:
+ /* The type of the hint : USE/FORCE/IGNORE */
+ enum index_hint_type type;
+ /* Where the hit applies to. A bitmask of INDEX_HINT_MASK_<place> values */
+ index_clause_map clause;
+ /*
+ The index name. Empty (str=NULL) name represents an empty list
+ USE INDEX () clause
+ */
+ LEX_STRING key_name;
+
+ index_hint (enum index_hint_type type_arg, index_clause_map clause_arg,
+ char *str, uint length) :
+ type(type_arg), clause(clause_arg)
+ {
+ key_name.str= str;
+ key_name.length= length;
+ }
+};
+
+/*
The state of the lex parsing for selects
master and slaves are pointers to select_lex.
@@ -386,15 +427,12 @@
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<index_hint> *hints= 0,
LEX_STRING *option= 0);
virtual void set_lock_for_tables(thr_lock_type lock_type) {}
@@ -521,8 +559,7 @@
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 +682,7 @@
LEX_STRING *alias,
ulong table_options,
thr_lock_type flags= TL_UNLOCK,
- List<String> *use_index= 0,
- List<String> *ignore_index= 0,
+ List<index_hint> *hints= 0,
LEX_STRING *option= 0);
TABLE_LIST* get_table_list();
bool init_nested_join(THD *thd);
@@ -655,8 +691,6 @@
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 +730,33 @@
select lexes.
*/
void cleanup_all_joins(bool full);
+
+ void set_index_hint_type(enum index_hint_type type, index_clause_map clause);
+
+ /*
+ Add a index hint to the tagged list of hints. The type and clause of the
+ hint will be the current ones (set by set_index_hint())
+ */
+ bool add_index_hint (THD *thd, char *str, uint length);
+
+ /* make a list to hold index hints */
+ void alloc_index_hints (THD *thd);
+ /* read and clear the index hints */
+ List<index_hint>* pop_index_hints(void)
+ {
+ List<index_hint> *hints= index_hints;
+ index_hints= NULL;
+ return hints;
+ }
+
+ void clear_index_hints(void) { index_hints= NULL; }
+
+private:
+ /* current index hint kind. used in filling up index_hints */
+ enum index_hint_type current_index_hint_type;
+ index_clause_map current_index_hint_clause;
+ /* a list of USE/FORCE/IGNORE INDEX */
+ List<index_hint> *index_hints;
};
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-03-06 10:21:55 +02:00
@@ -2382,8 +2382,7 @@
/* '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;
@@ -6336,8 +6335,7 @@
LEX_STRING *alias,
ulong table_options,
thr_lock_type lock_type,
- List<String> *use_index_arg,
- List<String> *ignore_index_arg,
+ List<index_hint> *index_hints_arg,
LEX_STRING *option)
{
register TABLE_LIST *ptr;
@@ -6412,12 +6410,7 @@
}
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-03-06 10:21:55 +02:00
@@ -165,13 +165,15 @@
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,15 @@
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);
+ 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 +1116,9 @@
((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 +1220,7 @@
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 +1237,7 @@
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 +1264,9 @@
{
/* 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 +1459,9 @@
(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 +1637,7 @@
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 +1853,8 @@
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 +3852,7 @@
/* 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->covering_keys.is_set(key))
{
/* we can use only index tree */
uint keys_per_block= table->file->stats.block_size/2/
@@ -4009,7 +4019,7 @@
/* 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->covering_keys.is_set(key))
{
/* we can use only index tree */
uint keys_per_block= table->file->stats.block_size/2/
@@ -4068,7 +4078,7 @@
!(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->covering_keys.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 +5975,7 @@
(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 +5992,7 @@
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->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread)
{
table->key_read=1;
@@ -6000,7 +6010,7 @@
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->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread)
{
table->key_read=1;
@@ -6017,7 +6027,7 @@
}
delete tab->quick;
tab->quick=0;
- if (table->used_keys.is_set(tab->ref.key) &&
+ if (table->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread)
{
table->key_read=1;
@@ -6103,15 +6113,15 @@
{
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->covering_keys.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->covering_keys.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->covering_keys);
tab->read_first_record= join_read_first;
tab->type=JT_NEXT; // Read with index_first / index_next
}
@@ -9179,7 +9189,7 @@
table->copy_blobs= 1;
table->in_use= thd;
table->quick_keys.init();
- table->used_keys.init();
+ table->covering_keys.init();
table->keys_in_use_for_query.init();
table->s= share;
@@ -10804,7 +10814,7 @@
}
else
{
- if (!table->key_read && table->used_keys.is_set(tab->ref.key) &&
+ if (!table->key_read && table->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread &&
(int) table->reginfo.lock_type <= (int) TL_READ_HIGH_PRIORITY)
{
@@ -11109,7 +11119,7 @@
{
int error;
TABLE *table=tab->table;
- if (!table->key_read && table->used_keys.is_set(tab->index) &&
+ if (!table->key_read && table->covering_keys.is_set(tab->index) &&
!table->no_keyread)
{
table->key_read=1;
@@ -11148,7 +11158,7 @@
{
TABLE *table=tab->table;
int error;
- if (!table->key_read && table->used_keys.is_set(tab->index) &&
+ if (!table->key_read && table->covering_keys.is_set(tab->index) &&
!table->no_keyread)
{
table->key_read=1;
@@ -12170,7 +12180,7 @@
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 +12194,16 @@
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;
+
+ /*
+ 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
+ */
+ if (table->key_read ||
+ (table->covering_keys.is_set(tab->index) && !table->no_keyread))
+ usable_keys.set_bit (tab->index);
for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
{
@@ -12244,8 +12261,8 @@
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->covering_keys.is_set(ref_key))
+ usable_keys.intersect(table->covering_keys);
if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
&usable_keys)) < MAX_KEY)
{
@@ -12360,7 +12377,7 @@
if (select_limit >= table->file->stats.records)
{
keys= *table->file->keys_to_use_for_scanning();
- keys.merge(table->used_keys);
+ keys.merge(table->covering_keys);
/*
We are adding here also the index specified in FORCE INDEX clause,
@@ -12388,7 +12405,7 @@
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->covering_keys.is_set(nr))
{
table->key_read=1;
table->file->extra(HA_EXTRA_KEYREAD);
@@ -12414,6 +12431,8 @@
filesort_limit Max number of rows that needs to be sorted
select_limit Max number of rows in final output
Used to decide if we should use index or not
+ is_order_by true if we are sorting on ORDER BY, false if GROUP BY
+ Used to decide if we should use index or not
IMPLEMENTATION
@@ -12432,7 +12451,8 @@
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 +12473,9 @@
*/
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 +15069,7 @@
/* 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->covering_keys.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-03-06 10:21:55 +02:00
@@ -2269,8 +2269,7 @@
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 @@
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-03-06 10:21:55 +02:00
@@ -130,7 +130,7 @@
#endif
uint table_count= 0;
ha_rows updated, found;
- key_map old_used_keys;
+ key_map old_covering_keys;
TABLE *table;
SQL_SELECT *select;
READ_RECORD info;
@@ -168,8 +168,8 @@
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->covering_keys" based on the WHERE */
+ table->covering_keys= table->s->keys_in_use;
table->quick_keys.clear_all();
#ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -179,7 +179,7 @@
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_covering_keys= table->covering_keys; // 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 @@
limit= 0; // Impossible WHERE
}
// Don't count on usage of 'only index' when calculating which key to use
- table->used_keys.clear_all();
+ table->covering_keys.clear_all();
#ifdef WITH_PARTITION_STORAGE_ENGINE
if (prune_partitions(thd, table, conds))
@@ -303,7 +303,7 @@
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_covering_keys.is_set(used_index))
{
table->key_read=1;
table->mark_columns_used_by_index(used_index);
@@ -1091,7 +1091,7 @@
}
/*
- We have to check values after setup_tables to get used_keys right in
+ We have to check values after setup_tables to get covering_keys right in
reference tables
*/
@@ -1118,7 +1118,7 @@
update.link_in_list((byte*) tl, (byte**) &tl->next_local);
tl->shared= table_count++;
table->no_keyread=1;
- table->used_keys.clear_all();
+ table->covering_keys.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-03-06 10:21:55 +02:00
@@ -150,6 +150,7 @@
struct st_lex *lex;
sp_head *sphead;
struct p_elem_val *p_elem_value;
+ enum index_hint_type index_hint;
}
%{
@@ -820,7 +821,7 @@
btree_or_rtree
%type <string_list>
- key_usage_list using_list
+ using_list
%type <key_part>
key_part
@@ -890,7 +891,7 @@
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
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
@@ -924,6 +925,8 @@
%type <spblock> sp_decls sp_decl
%type <lex> sp_cursor_stmt
%type <spname> sp_name
+%type <index_hint> index_hint_type
+%type <num> index_hint_clause
%type <NONE>
'-' '+' '*' '/' '%' '(' ')'
@@ -5523,12 +5526,8 @@
assign_to_keycache:
table_ident cache_keys_spec
{
- LEX *lex=Lex;
- 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))
+ if (!Select->add_table_to_list(YYTHD, $1, NULL, 0, TL_READ,
+ Select->pop_index_hints()))
YYABORT;
}
;
@@ -5555,33 +5554,26 @@
preload_keys:
table_ident cache_keys_spec opt_ignore_leaves
{
- LEX *lex=Lex;
- 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))
+ if (!Select->add_table_to_list(YYTHD, $1, NULL, $3, TL_READ,
+ Select->pop_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.alloc_index_hints(YYTHD);
+ Select->set_index_hint_type(INDEX_HINT_USE,
+ global_system_variables.old_mode ?
+ INDEX_HINT_MASK_JOIN :
+ INDEX_HINT_MASK_ALL);
}
+ 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_or_index '(' opt_key_usage_list ')'
;
opt_ignore_leaves:
@@ -6949,20 +6941,16 @@
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
{
- LEX *lex= Lex;
- SELECT_LEX *sel= lex->current_select;
- 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())))
+ if (!($$= Select->add_table_to_list(YYTHD, $2, $3,
+ Select->get_table_join_options(),
+ Lex->lock_option,
+ Select->pop_index_hints())))
YYABORT;
- sel->add_joined_table($$);
+ Select->add_joined_table($$);
}
| '{' ident table_ref LEFT OUTER JOIN_SYM table_ref
ON
@@ -7031,8 +7019,7 @@
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,52 +7118,67 @@
/* empty */ {}
| OUTER {};
+index_hint_clause:
+ /* empty */
+ {
+ $$= global_system_variables.old_mode ?
+ INDEX_HINT_MASK_JOIN : INDEX_HINT_MASK_ALL;
+ }
+ | FOR_SYM JOIN_SYM { $$= INDEX_HINT_MASK_JOIN; }
+ | FOR_SYM ORDER_SYM BY { $$= INDEX_HINT_MASK_ORDER; }
+ | FOR_SYM GROUP BY { $$= INDEX_HINT_MASK_GROUP; }
+ ;
+
+index_hint_type:
+ FORCE_SYM { $$= INDEX_HINT_FORCE; }
+ | IGNORE_SYM { $$= INDEX_HINT_IGNORE; }
+ ;
+
+index_hint_definition:
+ index_hint_type key_or_index index_hint_clause
+ {
+ Select->set_index_hint_type($1, $3);
+ }
+ '(' key_usage_list ')';
+ | USE_SYM key_or_index index_hint_clause
+ {
+ Select->set_index_hint_type(INDEX_HINT_USE, $3);
+ }
+ '(' opt_key_usage_list ')';
+
+
+index_hints_list:
+ index_hint_definition
+ | index_hints_list index_hint_definition
+ ;
+
+opt_index_hints_list:
+ /* empty */
+ | { Select->alloc_index_hints(YYTHD); } index_hints_list
+ ;
+
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_index_hints_list
+ ;
-key_usage_list:
- key_or_index { Select->interval_list.empty(); }
- '(' key_list_or_empty ')'
- { $$= &Select->interval_list; }
+opt_key_usage_list:
+ /* empty */ { Select->add_index_hint(YYTHD, NULL, 0); }
+ | key_usage_list {}
;
-key_list_or_empty:
- /* empty */ {}
- | key_usage_list2 {}
- ;
+key_usage_element:
+ ident { Select->add_index_hint(YYTHD, $1.str, $1.length); }
+ | PRIMARY_SYM
+ {
+ Select->add_index_hint(YYTHD, (char *)"PRIMARY", 7);
+ }
+ ;
-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)); }
- | ident
- { Select->
- interval_list.push_back(new (YYTHD->mem_root) String((const char*) $1.str, $1.length,
- system_charset_info)); }
- | PRIMARY_SYM
- { Select->
- interval_list.push_back(new (YYTHD->mem_root) String("PRIMARY", 7,
- system_charset_info)); };
+key_usage_list:
+ key_usage_element
+ | key_usage_list ',' key_usage_element
+ ;
using_list:
ident
--- 1.262/sql/table.cc 2006-12-03 18:02:54 +02:00
+++ 1.263/sql/table.cc 2007-03-06 10:21:55 +02:00
@@ -1354,7 +1354,7 @@
if (!(outparam->alias= my_strdup(alias, MYF(MY_WME))))
goto err;
outparam->quick_keys.init();
- outparam->used_keys.init();
+ outparam->covering_keys.init();
outparam->keys_in_use_for_query.init();
/* Allocate handler */
@@ -4131,6 +4131,175 @@
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
+ The parser collects the index hints for each table in a "tagged list"
+ (st_table_list::index_hints). Using the information in this tagged list
+ this function sets the members st_table::keys_in_use_for_query,
+ st_table::keys_in_use_for_group_by, st_table::keys_in_use_for_order_by,
+ st_table::force_index and st_table::covering_keys.
+
+ Current implementation of the runtime does not allow mixing FORCE INDEX
+ and USE INDEX, so this is checked here. Then the FORCE INDEX list
+ (if non-empty) is appended to the USE INDEX list and a flag is set.
+
+ Multiple hints of the same kind are processed so that each clause
+ is applied to what is computed in the previous clause.
+ For example:
+ USE INDEX (i1) USE INDEX (i2)
+ is equivalent to
+ USE INDEX (i1,i2)
+ and means "consider only i1 and i2".
+
+ Similarly
+ USE INDEX () USE INDEX (i1)
+ is equivalent to
+ USE INDEX (i1)
+ and means "consider only the index i1"
+
+ It is OK to have the same index several times, e.g. "USE INDEX (i1,i1)" is
+ not an error.
+
+ Different kind of hints (USE/FORCE/IGNORE) are processed in the following
+ order:
+ 1. All indexes in USE (or FORCE) INDEX are added to the mask.
+ 2. All IGNORE INDEX
+
+ e.g. "USE INDEX i1, IGNORE INDEX i1, USE INDEX i1" will not use i1 at all
+ as if we had "USE INDEX i1, USE INDEX i1, IGNORE INDEX i1".
+
+ As an optimization 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 FROM GROUP/ORDER.
+
+ RETURN VALUE
+ FALSE no errors found
+ TRUE found and reported an error.
+*/
+bool st_table_list::process_index_hints(TABLE *table)
+{
+ /* initialize the result variables */
+ 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;
+
+ /* index hint list processing */
+ if (index_hints)
+ {
+ key_map index_join[INDEX_HINT_FORCE + 1];
+ key_map index_order[INDEX_HINT_FORCE + 1];
+ key_map index_group[INDEX_HINT_FORCE + 1];
+ index_hint *hint;
+ int type;
+ bool have_empty_use_join= FALSE, have_empty_use_order= FALSE,
+ have_empty_use_group= FALSE;
+ List_iterator <index_hint> iter(*index_hints);
+
+ /* initialize temporary variables used to collect hints of each kind */
+ for (type= INDEX_HINT_IGNORE; type <= INDEX_HINT_FORCE; type++)
+ {
+ index_join[type].clear_all();
+ index_order[type].clear_all();
+ index_group[type].clear_all();
+ }
+
+ /* iterate over the hints list */
+ while ((hint= iter++))
+ {
+ uint pos;
+
+ /* process empty USE INDEX () */
+ if (hint->type == INDEX_HINT_USE && !hint->key_name.str)
+ {
+ if (hint->clause & INDEX_HINT_MASK_JOIN)
+ {
+ index_join[hint->type].clear_all();
+ have_empty_use_join= TRUE;
+ }
+ if (hint->clause & INDEX_HINT_MASK_ORDER)
+ {
+ index_order[hint->type].clear_all();
+ have_empty_use_order= TRUE;
+ }
+ if (hint->clause & INDEX_HINT_MASK_GROUP)
+ {
+ index_group[hint->type].clear_all();
+ have_empty_use_group= TRUE;
+ }
+ continue;
+ }
+
+ /*
+ Check if an index with the given name exists and get his offset in
+ the keys bitmask for the table
+ */
+ if (table->s->keynames.type_names == 0 ||
+ (pos= find_type(&table->s->keynames, hint->key_name.str,
+ hint->key_name.length, 1)) <= 0)
+ {
+ my_error(ER_KEY_DOES_NOT_EXITS, MYF(0), hint->key_name.str, alias);
+ return 1;
+ }
+
+ pos--;
+
+ /* add to the appropriate clause mask */
+ if (hint->clause & INDEX_HINT_MASK_JOIN)
+ index_join[hint->type].set_bit (pos);
+ if (hint->clause & INDEX_HINT_MASK_ORDER)
+ index_order[hint->type].set_bit (pos);
+ if (hint->clause & INDEX_HINT_MASK_GROUP)
+ index_group[hint->type].set_bit (pos);
+ }
+
+ /* cannot mix USE INDEX and FORCE INDEX */
+ if ((!index_join[INDEX_HINT_FORCE].is_clear_all() ||
+ !index_order[INDEX_HINT_FORCE].is_clear_all() ||
+ !index_group[INDEX_HINT_FORCE].is_clear_all()) &&
+ (!index_join[INDEX_HINT_USE].is_clear_all() || have_empty_use_join ||
+ !index_order[INDEX_HINT_USE].is_clear_all() || have_empty_use_order ||
+ !index_group[INDEX_HINT_USE].is_clear_all() || have_empty_use_group))
+ {
+ my_error(ER_WRONG_USAGE, MYF(0), index_hint_type_name[INDEX_HINT_USE],
+ index_hint_type_name[INDEX_HINT_FORCE]);
+ return 1;
+ }
+
+ /* process FORCE INDEX as USE INDEX with a flag */
+ if (!index_join[INDEX_HINT_FORCE].is_clear_all() ||
+ !index_order[INDEX_HINT_FORCE].is_clear_all() ||
+ !index_group[INDEX_HINT_FORCE].is_clear_all())
+ {
+ table->force_index= TRUE;
+ index_join[INDEX_HINT_USE].merge(index_join[INDEX_HINT_FORCE]);
+ index_order[INDEX_HINT_USE].merge(index_order[INDEX_HINT_FORCE]);
+ index_group[INDEX_HINT_USE].merge(index_group[INDEX_HINT_FORCE]);
+ }
+
+ /* apply USE INDEX */
+ if (!index_join[INDEX_HINT_USE].is_clear_all() || have_empty_use_join)
+ table->keys_in_use_for_query= index_join[INDEX_HINT_USE];
+ if (!index_order[INDEX_HINT_USE].is_clear_all() || have_empty_use_order)
+ table->keys_in_use_for_order_by= index_order[INDEX_HINT_USE];
+ if (!index_group[INDEX_HINT_USE].is_clear_all() || have_empty_use_group)
+ table->keys_in_use_for_group_by= index_group[INDEX_HINT_USE];
+
+ /* apply IGNORE INDEX */
+ table->keys_in_use_for_query.subtract (index_join[INDEX_HINT_IGNORE]);
+ table->keys_in_use_for_order_by.subtract (index_order[INDEX_HINT_IGNORE]);
+ table->keys_in_use_for_group_by.subtract (index_group[INDEX_HINT_IGNORE]);
+ }
+
+ /* make sure covering_keys don't include indexes disabled with a hint */
+ table->covering_keys.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-03-06 10:21:55 +02:00
@@ -295,6 +295,12 @@
/* Information for one open table */
+enum index_hint_type
+{
+ INDEX_HINT_IGNORE,
+ INDEX_HINT_USE,
+ INDEX_HINT_FORCE
+};
struct st_table {
st_table() {} /* Remove gcc warning */
@@ -314,7 +320,18 @@
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;
+ /*
+ Map of keys that can be used to retrieve all data from this table
+ needed by the query without reading the row.
+ */
+ key_map covering_keys;
+ key_map quick_keys, merge_keys;
+ /* Map of keys that can be used to access the table */
+ key_map keys_in_use_for_query;
+ /* Map of keys that can be used to calculate GROUP BY without sorting */
+ key_map keys_in_use_for_group_by;
+ /* Map of keys that can be used to calculate ORDER BY without sorting */
+ key_map 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 +653,7 @@
(TABLE_LIST::join_using_fields != NULL)
*/
+class index_hint;
typedef struct st_table_list
{
st_table_list() {} /* Remove gcc warning */
@@ -692,7 +710,7 @@
*/
struct st_table_list *next_name_resolution_table;
/* Index names in a "... JOIN ... USE/IGNORE INDEX ..." clause. */
- List<String> *use_index, *ignore_index;
+ List<index_hint> *index_hints;
TABLE *table; /* opened table */
uint table_id; /* table id (from binlog) for opened table */
/*
@@ -865,6 +883,13 @@
*/
void reinit_before_use(THD *thd);
Item_subselect *containing_subselect();
+
+ /*
+ Compiles the tagged hints list and fills up st_table::keys_in_use_for_query,
+ st_table::keys_in_use_for_group_by, st_table::keys_in_use_for_order_by,
+ st_table::force_index and st_table::covering_keys.
+ */
+ 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-03-06 10:21:54 +02:00
@@ -933,12 +933,110 @@
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
+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);
+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
+FORCE INDEX (PRIMARY)
+IGNORE INDEX FOR GROUP BY (i2)
+IGNORE INDEX FOR ORDER BY (i2)
+USE INDEX (i2);
+ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX
+EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
+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 ();
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
+EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
+EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
+USE 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 i2 9 NULL 256 Using index
+EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
+FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL i2 4 NULL 257 Using index for group-by
+EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (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 (i2) 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 FOR GROUP BY (i2)
+USE INDEX FOR ORDER BY (i2)
+USE INDEX FOR JOIN (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 FOR JOIN (i2)
+USE INDEX FOR JOIN (i2)
+USE INDEX FOR JOIN (i2,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 1 FROM t1 WHERE a IN
+(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 256 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 256 Using where
+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
+EXPLAIN SELECT 1 FROM t2 WHERE a IN
+(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 256 Using where
+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-03-06 10:21:55 +02:00
@@ -706,10 +706,66 @@
# 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 ();
+EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
+--error ER_WRONG_USAGE
+EXPLAIN SELECT a FROM t1
+ FORCE INDEX (PRIMARY)
+ IGNORE INDEX FOR GROUP BY (i2)
+ IGNORE INDEX FOR ORDER BY (i2)
+ USE INDEX (i2);
+EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
+--error ER_PARSE_ERROR
+EXPLAIN SELECT a FROM t1 FORCE INDEX ();
+--error ER_PARSE_ERROR
+EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
+EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
+ USE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
+ FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
+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;
+EXPLAIN SELECT a FROM t1
+ USE INDEX FOR GROUP BY (i2)
+ USE INDEX FOR ORDER BY (i2)
+ USE INDEX FOR JOIN (i2);
-DROP TABLE t1;
+EXPLAIN SELECT a FROM t1
+ USE INDEX FOR JOIN (i2)
+ USE INDEX FOR JOIN (i2)
+ USE INDEX FOR JOIN (i2,i2);
+
+EXPLAIN SELECT 1 FROM t1 WHERE a IN
+ (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+
+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;
+
+EXPLAIN SELECT 1 FROM t2 WHERE a IN
+ (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+
+DROP TABLE t1, t2;
--- 1.10/mysql-test/r/endspace.result 2005-03-21 10:34:28 +02:00
+++ 1.11/mysql-test/r/endspace.result 2007-03-06 10:21:54 +02:00
@@ -25,10 +25,11 @@
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
-select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 ignore key (key1) where text1='teststring' or
+text1 like 'teststring_%' ORDER BY text1;
text1
-teststring
teststring
+teststring
select * from t1 where text1='teststring' or text1 like 'teststring_%';
text1
teststring
@@ -48,10 +49,11 @@
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
-select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 ignore key (key1) where text1='teststring' or
+text1 like 'teststring_%' ORDER BY text1;
text1
-teststring
teststring
+teststring
select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
concat('|', text1, '|')
|teststring |
@@ -132,10 +134,11 @@
drop table t1;
create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
-select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 ignore key (key1) where text1='teststring' or
+text1 like 'teststring_%' ORDER BY text1;
text1
-teststring
teststring
+teststring
select * from t1 where text1='teststring' or text1 like 'teststring_%';
text1
teststring
--- 1.5/mysql-test/t/endspace.test 2005-07-28 16:12:33 +03:00
+++ 1.6/mysql-test/t/endspace.test 2007-03-06 10:21:54 +02:00
@@ -16,7 +16,8 @@
create table t1 (text1 varchar(32) not NULL, KEY key1 (text1));
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
check table t1;
-select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 ignore key (key1) where text1='teststring' or
+ text1 like 'teststring_%' ORDER BY text1;
select * from t1 where text1='teststring' or text1 like 'teststring_%';
select * from t1 where text1='teststring' or text1 > 'teststring\t';
select * from t1 order by text1;
@@ -24,7 +25,8 @@
alter table t1 modify text1 char(32) binary not null;
check table t1;
-select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 ignore key (key1) where text1='teststring' or
+ text1 like 'teststring_%' ORDER BY text1;
select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
select text1, length(text1) from t1 order by text1;
@@ -57,7 +59,8 @@
create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
-select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 ignore key (key1) where text1='teststring' or
+ text1 like 'teststring_%' ORDER BY text1;
select * from t1 where text1='teststring' or text1 like 'teststring_%';
select * from t1 where text1='teststring' or text1 >= 'teststring\t';
select * from t1 order by text1;
--- 1.53/sql/sql_help.cc 2006-06-04 18:52:14 +03:00
+++ 1.54/sql/sql_help.cc 2007-03-06 10:21:55 +02:00
@@ -568,7 +568,7 @@
cond->fix_fields(thd, &cond); // can never fail
/* Assume that no indexes cover all required fields */
- table->used_keys.clear_all();
+ table->covering_keys.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 | 6 Mar |
