summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/endspace.result15
-rw-r--r--mysql-test/r/group_by.result112
-rw-r--r--mysql-test/t/endspace.test9
-rw-r--r--mysql-test/t/group_by.test68
-rw-r--r--sql/item.cc2
-rw-r--r--sql/mysql_priv.h3
-rw-r--r--sql/mysqld.cc7
-rw-r--r--sql/mysqld.cc.rej17
-rw-r--r--sql/opt_range.cc8
-rw-r--r--sql/sql_base.cc37
-rw-r--r--sql/sql_class.h6
-rw-r--r--sql/sql_delete.cc4
-rw-r--r--sql/sql_help.cc2
-rw-r--r--sql/sql_lex.cc91
-rw-r--r--sql/sql_lex.h81
-rw-r--r--sql/sql_parse.cc13
-rw-r--r--sql/sql_parse.cc.rej166
-rw-r--r--sql/sql_select.cc96
-rw-r--r--sql/sql_show.cc6
-rw-r--r--sql/sql_update.cc16
-rw-r--r--sql/sql_yacc.yy158
-rw-r--r--sql/table.cc171
-rw-r--r--sql/table.cc.rej17
-rw-r--r--sql/table.h29
-rw-r--r--storage/myisam/ha_myisam.cc49
25 files changed, 726 insertions, 457 deletions
diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result
index 0e68418a80f..d7135fe3e2c 100644
--- a/mysql-test/r/endspace.result
+++ b/mysql-test/r/endspace.result
@@ -25,10 +25,11 @@ insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
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 @@ alter table t1 modify text1 char(32) binary not null;
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 @@ concat('|', text1, '|')
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
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 663ef6cced4..14f517a9520 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -933,12 +933,110 @@ 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
+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;
diff --git a/mysql-test/t/endspace.test b/mysql-test/t/endspace.test
index c4d53450910..b223c683cde 100644
--- a/mysql-test/t/endspace.test
+++ b/mysql-test/t/endspace.test
@@ -16,7 +16,8 @@ drop table if exists t1;
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 @@ explain select * from t1 order by text1;
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 @@ 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;
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;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 7f887335753..96ca79e15b5 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -706,10 +706,66 @@ 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 ();
+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 FROM t1
+ USE INDEX FOR GROUP BY (i2)
+ USE INDEX FOR ORDER BY (i2)
+ USE INDEX FOR JOIN (i2);
+
+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));
-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;
+DROP TABLE t1, t2;
diff --git a/sql/item.cc b/sql/item.cc
index e2ab28dd452..0bd5e70773a 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3828,7 +3828,7 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
{
/* 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);
}
}
}
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 812268fc92c..410dbd71304 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1957,7 +1957,6 @@ inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr)
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 @@ inline void setup_table_map(TABLE *table, TABLE_LIST *table_list, uint tablenr)
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();
}
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 96dfb3d01ec..a8406451b75 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -4869,7 +4869,8 @@ enum options_mysqld
OPT_PORT_OPEN_TIMEOUT,
OPT_GENERAL_LOG,
OPT_SLOW_LOG,
- OPT_MERGE
+ OPT_MERGE,
+ OPT_OLD_MODE
};
@@ -6080,6 +6081,10 @@ The minimum value for this variable is 4096.",
(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,
diff --git a/sql/mysqld.cc.rej b/sql/mysqld.cc.rej
deleted file mode 100644
index 62f0357622d..00000000000
--- a/sql/mysqld.cc.rej
+++ /dev/null
@@ -1,17 +0,0 @@
-***************
-*** 5316,5322 ****
- (gptr*) &locked_in_memory, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
- {"merge", OPT_MERGE, "Enable Merge storage engine. Disable with \
- --skip-merge.",
-! (gptr*) &opt_merge, (gptr*) &opt_merge, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0},
- {"myisam-recover", OPT_MYISAM_RECOVER,
- "Syntax: myisam-recover[=option[,option...]], where option can be DEFAULT, BACKUP, FORCE or QUICK.",
- (gptr*) &myisam_recover_options_str, (gptr*) &myisam_recover_options_str, 0,
---- 5336,5342 ----
- (gptr*) &locked_in_memory, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
- {"merge", OPT_MERGE, "Enable Merge storage engine. Disable with \
- --skip-merge.",
-! (gptr*) &opt_merge, (gptr*) &opt_merge, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0},
- {"myisam-recover", OPT_MYISAM_RECOVER,
- "Syntax: myisam-recover[=option[,option...]], where option can be DEFAULT, BACKUP, FORCE or QUICK.",
- (gptr*) &myisam_recover_options_str, (gptr*) &myisam_recover_options_str, 0,
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index fa575e73c39..e8c37856151 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2109,9 +2109,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
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(&param, records,
key_for_use) +
(double) records / TIME_FOR_COMPARE);
@@ -4646,7 +4646,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
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 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree)
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;
/*
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 3fd09d909a4..e7a0a9ed89e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1792,8 +1792,6 @@ bool reopen_name_locked_table(THD* thd, TABLE_LIST* 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= 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_list, MEM_ROOT *mem_root,
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(THD *thd, Field *field, TABLE *table)
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 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
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 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
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 @@ bool setup_tables(THD *thd, Name_resolution_context *context,
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 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
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 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
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++;
}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index de4a394d53c..7c61bdbb14e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -240,6 +240,11 @@ struct system_variables
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 @@ struct system_variables
DATE_TIME_FORMAT *datetime_format;
DATE_TIME_FORMAT *time_format;
my_bool sysdate_is_now;
+
};
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index df24dad2d4c..085b8348da2 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -117,7 +117,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
/* 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 @@ multi_delete::initialize_tables(JOIN *join)
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
diff --git a/sql/sql_help.cc b/sql/sql_help.cc
index 69d21f8b7bb..adca77d86c4 100644
--- a/sql/sql_help.cc
+++ b/sql/sql_help.cc
@@ -568,7 +568,7 @@ SQL_SELECT *prepare_simple_select(THD *thd, Item *cond,
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)) ||
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d156973a790..d4906122f76 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -70,6 +70,17 @@ static uchar to_upper_lex[]=
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 @@ 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 +1192,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,14 +1406,11 @@ bool st_select_lex_node::inc_in_sum_expr() { return 1; }
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 @@ 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 +2253,61 @@ void st_select_lex::fix_prepare_information(THD *thd, Item **conds,
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));
+}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 7fd60cbfa58..0e91b2a786b 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -209,6 +209,47 @@ enum tablespace_op_type
};
/*
+ 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 @@ 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<index_hint> *hints= 0,
LEX_STRING *option= 0);
virtual void set_lock_for_tables(thr_lock_type lock_type) {}
@@ -521,8 +559,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 +682,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<index_hint> *hints= 0,
LEX_STRING *option= 0);
TABLE_LIST* get_table_list();
bool init_nested_join(THD *thd);
@@ -655,8 +691,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 +730,33 @@ public:
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;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 1c46c3189d9..5fa3c22408f 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2382,8 +2382,7 @@ int prepare_schema_table(THD *thd, LEX *lex, Table_ident *table_ident,
/* '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 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
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 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
}
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)
diff --git a/sql/sql_parse.cc.rej b/sql/sql_parse.cc.rej
deleted file mode 100644
index 6e2bd03867d..00000000000
--- a/sql/sql_parse.cc.rej
+++ /dev/null
@@ -1,166 +0,0 @@
-***************
-*** 67,109 ****
- static void decrease_user_connections(USER_CONN *uc);
- #endif /* NO_EMBEDDED_ACCESS_CHECKS */
- static bool check_multi_update_lock(THD *thd);
-- static void remove_escape(char *name);
- static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables);
-
- const char *any_db="*any*"; // Special symbol for check_access
-
-! LEX_STRING command_name[]={
-! (char *)STRING_WITH_LEN("Sleep"),
-! (char *)STRING_WITH_LEN("Quit"),
-! (char *)STRING_WITH_LEN("Init DB"),
-! (char *)STRING_WITH_LEN("Query"),
-! (char *)STRING_WITH_LEN("Field List"),
-! (char *)STRING_WITH_LEN("Create DB"),
-! (char *)STRING_WITH_LEN("Drop DB"),
-! (char *)STRING_WITH_LEN("Refresh"),
-! (char *)STRING_WITH_LEN("Shutdown"),
-! (char *)STRING_WITH_LEN("Statistics"),
-! (char *)STRING_WITH_LEN("Processlist"),
-! (char *)STRING_WITH_LEN("Connect"),
-! (char *)STRING_WITH_LEN("Kill"),
-! (char *)STRING_WITH_LEN("Debug"),
-! (char *)STRING_WITH_LEN("Ping"),
-! (char *)STRING_WITH_LEN("Time"),
-! (char *)STRING_WITH_LEN("Delayed insert"),
-! (char *)STRING_WITH_LEN("Change user"),
-! (char *)STRING_WITH_LEN("Binlog Dump"),
-! (char *)STRING_WITH_LEN("Table Dump"),
-! (char *)STRING_WITH_LEN("Connect Out"),
-! (char *)STRING_WITH_LEN("Register Slave"),
-! (char *)STRING_WITH_LEN("Prepare"),
-! (char *)STRING_WITH_LEN("Execute"),
-! (char *)STRING_WITH_LEN("Long Data"),
-! (char *)STRING_WITH_LEN("Close stmt"),
-! (char *)STRING_WITH_LEN("Reset stmt"),
-! (char *)STRING_WITH_LEN("Set option"),
-! (char *)STRING_WITH_LEN("Fetch"),
-! (char *)STRING_WITH_LEN("Daemon"),
-! (char *)STRING_WITH_LEN("Error") // Last command number
- };
-
- const char *xa_state_names[]={
---- 67,108 ----
- static void decrease_user_connections(USER_CONN *uc);
- #endif /* NO_EMBEDDED_ACCESS_CHECKS */
- static bool check_multi_update_lock(THD *thd);
- static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables);
-
- const char *any_db="*any*"; // Special symbol for check_access
-
-! const LEX_STRING command_name[]={
-! C_STRING_WITH_LEN("Sleep"),
-! C_STRING_WITH_LEN("Quit"),
-! C_STRING_WITH_LEN("Init DB"),
-! C_STRING_WITH_LEN("Query"),
-! C_STRING_WITH_LEN("Field List"),
-! C_STRING_WITH_LEN("Create DB"),
-! C_STRING_WITH_LEN("Drop DB"),
-! C_STRING_WITH_LEN("Refresh"),
-! C_STRING_WITH_LEN("Shutdown"),
-! C_STRING_WITH_LEN("Statistics"),
-! C_STRING_WITH_LEN("Processlist"),
-! C_STRING_WITH_LEN("Connect"),
-! C_STRING_WITH_LEN("Kill"),
-! C_STRING_WITH_LEN("Debug"),
-! C_STRING_WITH_LEN("Ping"),
-! C_STRING_WITH_LEN("Time"),
-! C_STRING_WITH_LEN("Delayed insert"),
-! C_STRING_WITH_LEN("Change user"),
-! C_STRING_WITH_LEN("Binlog Dump"),
-! C_STRING_WITH_LEN("Table Dump"),
-! C_STRING_WITH_LEN("Connect Out"),
-! C_STRING_WITH_LEN("Register Slave"),
-! C_STRING_WITH_LEN("Prepare"),
-! C_STRING_WITH_LEN("Execute"),
-! C_STRING_WITH_LEN("Long Data"),
-! C_STRING_WITH_LEN("Close stmt"),
-! C_STRING_WITH_LEN("Reset stmt"),
-! C_STRING_WITH_LEN("Set option"),
-! C_STRING_WITH_LEN("Fetch"),
-! C_STRING_WITH_LEN("Daemon"),
-! C_STRING_WITH_LEN("Error") // Last command number
- };
-
- const char *xa_state_names[]={
-***************
-*** 1738,1744 ****
- password. New clients send the size (1 byte) + string (not null
- terminated, so also '\0' for empty string).
- */
-! char db_buff[NAME_LEN+1]; // buffer to store db in utf8
- char *db= passwd;
- uint passwd_len= thd->client_capabilities & CLIENT_SECURE_CONNECTION ?
- *passwd++ : strlen(passwd);
---- 1736,1742 ----
- password. New clients send the size (1 byte) + string (not null
- terminated, so also '\0' for empty string).
- */
-! char db_buff[NAME_LEN+1]; // buffer to store db in utf8
- char *db= passwd;
- uint passwd_len= thd->client_capabilities & CLIENT_SECURE_CONNECTION ?
- *passwd++ : strlen(passwd);
-***************
-*** 2315,2321 ****
- DBUG_RETURN(1);
- }
- db= lex->select_lex.db;
-- remove_escape(db); // Fix escaped '_'
- if (check_db_name(db))
- {
- my_error(ER_WRONG_DB_NAME, MYF(0), db);
---- 2312,2317 ----
- DBUG_RETURN(1);
- }
- db= lex->select_lex.db;
- if (check_db_name(db))
- {
- my_error(ER_WRONG_DB_NAME, MYF(0), db);
-***************
-*** 6310,6345 ****
- }
-
-
-- /* Fix escaping of _, % and \ in database and table names (for ODBC) */
--
-- static void remove_escape(char *name)
-- {
-- if (!*name) // For empty DB names
-- return;
-- char *to;
-- #ifdef USE_MB
-- char *strend=name+(uint) strlen(name);
-- #endif
-- for (to=name; *name ; name++)
-- {
-- #ifdef USE_MB
-- int l;
-- if (use_mb(system_charset_info) &&
-- (l = my_ismbchar(system_charset_info, name, strend)))
-- {
-- while (l--)
-- *to++ = *name++;
-- name--;
-- continue;
-- }
-- #endif
-- if (*name == '\\' && name[1])
-- name++; // Skip '\\'
-- *to++= *name;
-- }
-- *to=0;
-- }
--
- /****************************************************************************
- ** save order by and tables in own lists
- ****************************************************************************/
---- 6296,6301 ----
- }
-
-
- /****************************************************************************
- ** save order by and tables in own lists
- ****************************************************************************/
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9a64055f2e3..f05bf555816 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -165,13 +165,15 @@ static COND *make_cond_for_table(COND *cond,table_map table,
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::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);
+ 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 @@ 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 +1220,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 +1237,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 +1264,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 +1459,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 +1637,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 +1853,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 +3852,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->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 @@ 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->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 @@ 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->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 @@ make_join_readinfo(JOIN *join, ulonglong options)
(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 @@ make_join_readinfo(JOIN *join, ulonglong options)
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 @@ make_join_readinfo(JOIN *join, ulonglong options)
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 @@ make_join_readinfo(JOIN *join, ulonglong options)
}
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 @@ make_join_readinfo(JOIN *join, ulonglong options)
{
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 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
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 @@ join_read_const_table(JOIN_TAB *tab, POSITION *pos)
}
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 @@ 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->covering_keys.is_set(tab->index) &&
!table->no_keyread)
{
table->key_read=1;
@@ -11148,7 +11158,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->covering_keys.is_set(tab->index) &&
!table->no_keyread)
{
table->key_read=1;
@@ -12170,7 +12180,7 @@ find_field_in_item_list (Field *field, void *data)
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 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
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 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
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 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
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 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
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 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
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 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
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 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
*/
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 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
/* 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)
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 0ebccba43ca..622a415a743 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2269,8 +2269,7 @@ int make_table_list(THD *thd, SELECT_LEX *sel,
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_LEX *sel,
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);
}
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index a379ea66db6..41ddd89f759 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -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_covering_keys;
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->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 @@ 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_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 @@ 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->covering_keys.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_covering_keys.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> &not_used_values,
}
/*
- 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 @@ int multi_update::prepare(List<Item> &not_used_values,
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;
}
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 704c505ded9..b067443ba8c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -150,6 +150,7 @@ static bool is_native_function(THD *thd, const LEX_STRING *name)
struct st_lex *lex;
sp_head *sphead;
struct p_elem_val *p_elem_value;
+ enum index_hint_type index_hint;
}
%{
@@ -820,7 +821,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
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 **b, ulong *yystacksize);
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 @@ END_OF_INPUT
%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 @@ keycache_list:
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_list:
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 @@ 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
{
- 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 @@ 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,52 +7118,67 @@ opt_outer:
/* 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
diff --git a/sql/table.cc b/sql/table.cc
index 52074ee1e7c..9d74affb788 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1354,7 +1354,7 @@ int open_table_from_share(THD *thd, TABLE_SHARE *share, const char *alias,
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 */
@@ -4133,6 +4133,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;
+}
+
/*****************************************************************************
** Instansiate templates
*****************************************************************************/
diff --git a/sql/table.cc.rej b/sql/table.cc.rej
deleted file mode 100644
index fd728ba9965..00000000000
--- a/sql/table.cc.rej
+++ /dev/null
@@ -1,17 +0,0 @@
-***************
-*** 2246,2252 ****
-
- bool check_db_name(char *name)
- {
-! char *start=name;
- /* Used to catch empty names and names with end space */
- bool last_char_is_space= TRUE;
-
---- 2257,2263 ----
-
- bool check_db_name(char *name)
- {
-! char *start= name;
- /* Used to catch empty names and names with end space */
- bool last_char_is_space= TRUE;
-
diff --git a/sql/table.h b/sql/table.h
index 13666c82f4b..0dfca83d454 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -295,6 +295,12 @@ typedef struct st_table_share
/* 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 @@ 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;
+ /*
+ 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 @@ public:
(TABLE_LIST::join_using_fields != NULL)
*/
+class index_hint;
typedef struct st_table_list
{
st_table_list() {} /* Remove gcc warning */
@@ -692,7 +710,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<index_hint> *index_hints;
TABLE *table; /* opened table */
uint table_id; /* table id (from binlog) for opened table */
/*
@@ -866,6 +884,13 @@ typedef struct st_table_list
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);
bool prep_where(THD *thd, Item **conds, bool no_where_clause);
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);
View Lorry Controller Status