summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/distinct.result12
-rw-r--r--mysql-test/r/group_by.result4
-rw-r--r--mysql-test/r/heap.result2
-rw-r--r--mysql-test/r/join.result4
-rw-r--r--mysql-test/r/key_diff.result2
-rw-r--r--mysql-test/r/myisam.result10
-rw-r--r--mysql-test/r/order_by.result6
-rw-r--r--mysql-test/r/select_safe.result23
-rw-r--r--mysql-test/t/distinct.test2
-rw-r--r--mysql-test/t/join.test5
-rw-r--r--mysql-test/t/select_safe.test15
-rw-r--r--sql/handler.h37
-rw-r--r--sql/item_cmpfunc.cc4
-rw-r--r--sql/mysqld.cc7
-rw-r--r--sql/set_var.cc4
-rw-r--r--sql/sql_class.h1
-rw-r--r--sql/sql_select.cc41
17 files changed, 114 insertions, 65 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 5f4f7cced1e..53a20eeea0b 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -161,7 +161,7 @@ SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
UserId
drop table t1;
CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
-INSERT INTO t1 VALUES (1,1),(2,1);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
CREATE TABLE t2 (a int(10) unsigned not null, key (A));
INSERT INTO t2 VALUES (1),(2);
CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
@@ -189,7 +189,7 @@ insert into t4 select * from t3;
insert into t3 select * from t4;
explain select distinct t1.a from t1,t3 where t1.a=t3.a;
table type possible_keys key key_len ref rows Extra
-t1 index PRIMARY PRIMARY 4 NULL 2 Using index; Using temporary
+t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
t3 ref a a 5 t1.a 10 Using where; Using index; Distinct
select distinct t1.a from t1,t3 where t1.a=t3.a;
a
@@ -200,16 +200,16 @@ select distinct 1 from t1,t3 where t1.a=t3.a;
1
explain SELECT distinct t1.a from t1;
table type possible_keys key key_len ref rows Extra
-t1 index NULL PRIMARY 4 NULL 2 Using index
+t1 index NULL PRIMARY 4 NULL 4 Using index
explain SELECT distinct t1.a from t1 order by a desc;
table type possible_keys key key_len ref rows Extra
-t1 index NULL PRIMARY 4 NULL 2 Using index
+t1 index NULL PRIMARY 4 NULL 4 Using index
explain SELECT t1.a from t1 group by a order by a desc;
table type possible_keys key key_len ref rows Extra
-t1 index NULL PRIMARY 4 NULL 2 Using index
+t1 index NULL PRIMARY 4 NULL 4 Using index
explain SELECT distinct t1.a from t1 order by a desc limit 1;
table type possible_keys key key_len ref rows Extra
-t1 index NULL PRIMARY 4 NULL 2 Using index
+t1 index NULL PRIMARY 4 NULL 4 Using index
explain SELECT distinct a from t3 order by a desc limit 2;
table type possible_keys key key_len ref rows Extra
t3 index NULL a 5 NULL 204 Using index
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 94e1ce59585..67e6bec09f6 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -534,11 +534,11 @@ a b
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
table type possible_keys key key_len ref rows Extra
t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
-t2 ALL a NULL NULL NULL 4 Using where
+t2 ALL a NULL NULL NULL 3 Using where
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
table type possible_keys key key_len ref rows Extra
t1 ALL NULL NULL NULL NULL 6 Using temporary
-t2 ALL a NULL NULL NULL 4 Using where
+t2 ALL a NULL NULL NULL 3 Using where
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index d8905085e34..a04ddf3f302 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -166,7 +166,7 @@ alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop
update t1 set new_col=btn;
explain select * from t1 where btn="a";
table type possible_keys key key_len ref rows Extra
-t1 ALL btn NULL NULL NULL 14 Using where
+t1 ALL btn NULL NULL NULL 11 Using where
explain select * from t1 where btn="a" and new_col="a";
table type possible_keys key key_len ref rows Extra
t1 ref btn btn 11 const,const 10 Using where
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index e063b5c3e02..039b6e1cba3 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -104,9 +104,7 @@ KEY category (category,county,state)
INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
-select a.id, b.category as catid, b.state as stateid, b.county as
-countyid from t1 a, t2 b where (a.token =
-'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
+select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
id catid stateid countyid
27 2 12 11
28 2 12 11
diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result
index 4eaccc696f9..2d4bc19474f 100644
--- a/mysql-test/r/key_diff.result
+++ b/mysql-test/r/key_diff.result
@@ -36,7 +36,7 @@ a a a a
explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
table type possible_keys key key_len ref rows Extra
t1 ALL a NULL NULL NULL 5
-t2 ALL b NULL NULL NULL 5 Using where
+t2 ALL b NULL NULL NULL 4 Using where
select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a;
a b a b
A B a a
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 9a653aff99e..c4368384bf8 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -335,12 +335,12 @@ t1 1 c_2 1 c A 5 NULL NULL YES BTREE
t1 1 c_2 2 a A 5 NULL NULL BTREE
explain select * from t1,t2 where t1.a=t2.a;
table type possible_keys key key_len ref rows Extra
-t1 ALL a NULL NULL NULL 5
-t2 ALL a NULL NULL NULL 2 Using where
+t2 ALL a NULL NULL NULL 2
+t1 ALL a NULL NULL NULL 4 Using where
explain select * from t1,t2 force index(a) where t1.a=t2.a;
table type possible_keys key key_len ref rows Extra
t2 ALL a NULL NULL NULL 2
-t1 ALL a NULL NULL NULL 5 Using where
+t1 ALL a NULL NULL NULL 4 Using where
explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
table type possible_keys key key_len ref rows Extra
t2 ALL a NULL NULL NULL 2
@@ -351,8 +351,8 @@ t2 ALL b NULL NULL NULL 2
t1 ref b b 5 t2.b 1 Using where
explain select * from t1,t2 force index(c) where t1.a=t2.a;
table type possible_keys key key_len ref rows Extra
-t1 ALL a NULL NULL NULL 5
-t2 ALL NULL NULL NULL NULL 2 Using where
+t2 ALL NULL NULL NULL NULL 2
+t1 ALL a NULL NULL NULL 4 Using where
explain select * from t1 where a=0 or a=2;
table type possible_keys key key_len ref rows Extra
t1 ALL a NULL NULL NULL 5 Using where
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index ff2dd93311c..64fac8af872 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -307,17 +307,17 @@ table type possible_keys key key_len ref rows Extra
t1 range a a 9 NULL 8 Using where; Using index
explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
table type possible_keys key key_len ref rows Extra
-t1 range a a 9 NULL 5 Using where; Using index
+t1 range a a 9 NULL 4 Using where; Using index
explain select * from t1 where a = 2 and b is null order by a desc,b desc;
table type possible_keys key key_len ref rows Extra
t1 ref a a 9 const,const 1 Using where; Using index; Using filesort
explain select * from t1 where a = 2 and (b is null or b > 0) order by a
desc,b desc;
table type possible_keys key key_len ref rows Extra
-t1 range a a 9 NULL 6 Using where; Using index
+t1 range a a 9 NULL 5 Using where; Using index
explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
table type possible_keys key key_len ref rows Extra
-t1 range a a 9 NULL 5 Using where; Using index
+t1 range a a 9 NULL 4 Using where; Using index
explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
table type possible_keys key key_len ref rows Extra
t1 range a a 9 NULL 2 Using where; Using index
diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result
index 3303f19d9c7..830390ebe3c 100644
--- a/mysql-test/r/select_safe.result
+++ b/mysql-test/r/select_safe.result
@@ -1,6 +1,6 @@
drop table if exists t1;
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
-create table t1 (a int auto_increment primary key, b char(20));
+create table t1 (a int auto_increment primary key, b char(20), key(b));
insert into t1 values(1,"test");
SELECT SQL_BUFFER_RESULT * from t1;
a b
@@ -13,7 +13,7 @@ a b
1 test
2 test2
update t1 set b="a" where a=1;
-select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
+select 1 from t1,t1 as t2,t1 as t3;
1
1
1
@@ -22,11 +22,9 @@ select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
update t1 set b="a";
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
update t1 set b="a" where b="test";
-You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
delete from t1;
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
delete from t1 where b="test";
-You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
delete from t1 where a+0=1;
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;
@@ -59,5 +57,22 @@ a b
2 test2
4 a
5 a
+SELECT @@MAX_SEEKS_FOR_KEY;
+@@max_seeks_for_key
+4294967295
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+table type possible_keys key key_len ref rows Extra
+t1 ALL b NULL NULL NULL 21
+t2 ALL b NULL NULL NULL 16 Using where
+set MAX_SEEKS_FOR_KEY=1;
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+table type possible_keys key key_len ref rows Extra
+t1 ALL b NULL NULL NULL 21
+t2 ref b b 21 t1.b 6 Using where
+SET MAX_SEEKS_FOR_KEY=DEFAULT;
drop table t1;
SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index 7f75b6b1687..ecce2409571 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -60,7 +60,7 @@ drop table t1;
#
CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
-INSERT INTO t1 VALUES (1,1),(2,1);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
CREATE TABLE t2 (a int(10) unsigned not null, key (A));
INSERT INTO t2 VALUES (1),(2);
CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 08cc5731723..19e04d2aa7e 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -98,10 +98,7 @@ CREATE TABLE t2 (
INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
-
-select a.id, b.category as catid, b.state as stateid, b.county as
-countyid from t1 a, t2 b where (a.token =
-'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
+select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id);
select a.id, b.category as catid, b.state as stateid, b.county as
countyid from t1 a, t2 b where (a.token =
'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id) order by a.id;
diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test
index a085cfee29d..049a35aa355 100644
--- a/mysql-test/t/select_safe.test
+++ b/mysql-test/t/select_safe.test
@@ -4,7 +4,7 @@
drop table if exists t1;
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
-create table t1 (a int auto_increment primary key, b char(20));
+create table t1 (a int auto_increment primary key, b char(20), key(b));
insert into t1 values(1,"test");
SELECT SQL_BUFFER_RESULT * from t1;
update t1 set b="a" where a=1;
@@ -12,7 +12,7 @@ delete from t1 where a=1;
insert into t1 values(1,"test"),(2,"test2");
SELECT SQL_BUFFER_RESULT * from t1;
update t1 set b="a" where a=1;
-select 1 from t1,t1 as t2,t1 as t3,t1 as t4;
+select 1 from t1,t1 as t2,t1 as t3;
# The following should give errors:
--error 1175
@@ -49,6 +49,17 @@ SELECT * from t1;
SET MAX_JOIN_SIZE=DEFAULT;
SELECT * from t1;
+#
+# Test MAX_SEEKS_FOR_KEY
+#
+SELECT @@MAX_SEEKS_FOR_KEY;
+analyze table t1;
+insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+set MAX_SEEKS_FOR_KEY=1;
+explain select * from t1,t1 as t2 where t1.b=t2.b;
+SET MAX_SEEKS_FOR_KEY=DEFAULT;
+
drop table t1;
SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
diff --git a/sql/handler.h b/sql/handler.h
index 56f63d1d521..dfcfa44fbcd 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -184,40 +184,41 @@ class handler :public Sql_alloc
{
protected:
struct st_table *table; /* The table definition */
- uint active_index;
public:
byte *ref; /* Pointer to current row */
byte *dupp_ref; /* Pointer to dupp row */
- uint ref_length; /* Length of ref (1-8 or the clustered
- key length) */
- uint block_size; /* index block size */
- ha_rows records; /* Records i datafilen */
- ha_rows deleted; /* Deleted records */
ulonglong data_file_length; /* Length off data file */
ulonglong max_data_file_length; /* Length off data file */
ulonglong index_file_length;
ulonglong max_index_file_length;
ulonglong delete_length; /* Free bytes */
ulonglong auto_increment_value;
- uint raid_type,raid_chunks;
+ ha_rows records; /* Records in table */
+ ha_rows deleted; /* Deleted records */
ulong raid_chunksize;
- uint errkey; /* Last dup key */
- uint sortkey, key_used_on_scan;
+ ulong mean_rec_length; /* physical reclength */
time_t create_time; /* When table was created */
time_t check_time;
time_t update_time;
- ulong mean_rec_length; /* physical reclength */
+ uint errkey; /* Last dup key */
+ uint sortkey, key_used_on_scan;
+ uint active_index;
+ /* Length of ref (1-8 or the clustered key length) */
+ uint ref_length;
+ uint block_size; /* index block size */
+ uint raid_type,raid_chunks;
FT_INFO *ft_handler;
bool auto_increment_column_changed;
- handler(TABLE *table_arg) : table(table_arg),active_index(MAX_REF_PARTS),
- ref(0),ref_length(sizeof(my_off_t)), block_size(0),records(0),deleted(0),
- data_file_length(0), max_data_file_length(0), index_file_length(0),
- delete_length(0), auto_increment_value(0), raid_type(0),
- key_used_on_scan(MAX_KEY),
- create_time(0), check_time(0), update_time(0), mean_rec_length(0),
- ft_handler(0)
+ handler(TABLE *table_arg) :table(table_arg),
+ ref(0), data_file_length(0), max_data_file_length(0), index_file_length(0),
+ delete_length(0), auto_increment_value(0),
+ records(0), deleted(0), mean_rec_length(0),
+ create_time(0), check_time(0), update_time(0),
+ key_used_on_scan(MAX_KEY), active_index(MAX_REF_PARTS),
+ ref_length(sizeof(my_off_t)), block_size(0),
+ raid_type(0), ft_handler(0)
{}
virtual ~handler(void) {}
int ha_open(const char *name, int mode, int test_if_locked);
@@ -227,7 +228,7 @@ public:
uint get_dup_key(int error);
void change_table_ptr(TABLE *table_arg) { table=table_arg; }
virtual double scan_time()
- { return ulonglong2double(data_file_length) / IO_SIZE + 1; }
+ { return ulonglong2double(data_file_length) / IO_SIZE + 2; }
virtual double read_time(uint index, uint ranges, ha_rows rows)
{ return rows2double(ranges+rows); }
virtual bool fast_key_read() { return 0;}
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 770fe3c0232..78a63d84c61 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1175,8 +1175,8 @@ Item_cond::fix_fields(THD *thd,TABLE_LIST *tables)
List_iterator<Item> li(list);
Item *item;
char buff[sizeof(char*)]; // Max local vars in function
- used_tables_cache=0;
- const_item_cache=0;
+ not_null_tables_cache= used_tables_cache= 0;
+ const_item_cache= 0;
/*
and_table_cache is the value that Item_cond_or() returns for
not_null_tables()
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 5613f1eeb07..f27ba435729 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3154,7 +3154,7 @@ enum options {
OPT_MAX_BINLOG_CACHE_SIZE, OPT_MAX_BINLOG_SIZE,
OPT_MAX_CONNECTIONS, OPT_MAX_CONNECT_ERRORS,
OPT_MAX_DELAYED_THREADS, OPT_MAX_HEP_TABLE_SIZE,
- OPT_MAX_JOIN_SIZE, OPT_MAX_SORT_LENGTH,
+ OPT_MAX_JOIN_SIZE, OPT_MAX_SORT_LENGTH, OPT_MAX_SEEKS_FOR_KEY,
OPT_MAX_TMP_TABLES, OPT_MAX_USER_CONNECTIONS,
OPT_MAX_WRITE_LOCK_COUNT, OPT_BULK_INSERT_BUFFER_SIZE,
OPT_MYISAM_BLOCK_SIZE, OPT_MYISAM_MAX_EXTRA_SORT_FILE_SIZE,
@@ -3832,6 +3832,11 @@ replicating a LOAD DATA INFILE command",
(gptr*) &global_system_variables.max_join_size,
(gptr*) &max_system_variables.max_join_size, 0, GET_HA_ROWS, REQUIRED_ARG,
~0L, 1, ~0L, 0, 1, 0},
+ { "max_seeks_for_key", OPT_MAX_SEEKS_FOR_KEY,
+ "Limit assumed max number of seeks when looking up rows based on a key",
+ (gptr*) &global_system_variables.max_seeks_for_key,
+ (gptr*) &max_system_variables.max_seeks_for_key, 0, GET_ULONG,
+ REQUIRED_ARG, ~0L, 1, ~0L, 0, 1, 0 },
{"max_sort_length", OPT_MAX_SORT_LENGTH,
"The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).",
(gptr*) &global_system_variables.max_sort_length,
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 32603ec51d9..3edcdbc1f7f 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -154,6 +154,8 @@ sys_var_thd_ulong sys_max_heap_table_size("max_heap_table_size",
sys_var_thd_ha_rows sys_max_join_size("max_join_size",
&SV::max_join_size,
fix_max_join_size);
+sys_var_thd_ulong sys_max_seeks_for_key("max_seeks_for_key",
+ &SV::max_seeks_for_key);
#ifndef TO_BE_DELETED /* Alias for max_join_size */
sys_var_thd_ha_rows sys_sql_max_join_size("sql_max_join_size",
&SV::max_join_size,
@@ -348,6 +350,7 @@ sys_var *sys_variables[]=
&sys_max_delayed_threads,
&sys_max_heap_table_size,
&sys_max_join_size,
+ &sys_max_seeks_for_key,
&sys_max_sort_length,
&sys_max_tmp_tables,
&sys_max_user_connections,
@@ -492,6 +495,7 @@ struct show_var_st init_vars[]= {
{sys_max_delayed_threads.name,(char*) &sys_max_delayed_threads, SHOW_SYS},
{sys_max_heap_table_size.name,(char*) &sys_max_heap_table_size, SHOW_SYS},
{sys_max_join_size.name, (char*) &sys_max_join_size, SHOW_SYS},
+ {sys_max_seeks_for_key.name, (char*) &sys_max_seeks_for_key, SHOW_SYS},
{sys_max_sort_length.name, (char*) &sys_max_sort_length, SHOW_SYS},
{sys_max_user_connections.name,(char*) &sys_max_user_connections, SHOW_SYS},
{sys_max_tmp_tables.name, (char*) &sys_max_tmp_tables, SHOW_SYS},
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 3246f77c77a..d1b2ef82ccb 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -310,6 +310,7 @@ struct system_variables
ulong tx_isolation;
ulong table_type;
ulong default_week_format;
+ ulong max_seeks_for_key;
my_bool log_warnings;
my_bool low_priority_updates;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 67bec77e245..f4c7a2ab031 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1271,14 +1271,14 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
/*
Set a max range of how many seeks we can expect when using keys
- This was (s->read_time*5), but this was too low with small rows
+ This is can't be to high as otherwise we are likely to use
+ table scan.
*/
- s->worst_seeks= (double) s->found_records / 5;
+ s->worst_seeks= min((double) s->found_records / 10,
+ (double) s->read_time*3);
if (s->worst_seeks < 2.0) // Fix for small tables
s->worst_seeks=2.0;
- /* if (s->type == JT_EQ_REF)
- continue; */
if (s->const_keys)
{
ha_rows records;
@@ -1884,6 +1884,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
best=best_time=records=DBL_MAX;
KEYUSE *best_key=0;
uint best_max_key_part=0;
+ my_bool found_constrain= 0;
if (s->keyuse)
{ /* Use key if possible */
@@ -1964,6 +1965,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
}
else
{
+ found_constrain= 1;
/*
Check if we found full key
*/
@@ -2000,16 +2002,18 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
records=2.0; // Can't be as good as a unique
}
}
+ /* Limit the number of matched rows */
+ tmp= records;
+ set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
if (table->used_keys & ((key_map) 1 << key))
{
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
(keyinfo->key_length+table->file->ref_length)+1;
- tmp=(record_count*(records+keys_per_block-1)/
- keys_per_block);
+ tmp=record_count*(tmp+keys_per_block-1)/keys_per_block;
}
else
- tmp=record_count*min(records,s->worst_seeks);
+ tmp=record_count*min(tmp,s->worst_seeks);
}
}
else
@@ -2039,7 +2043,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
{
/*
Assume that the first key part matches 1% of the file
- and that the hole key matches 10 (dupplicates) or 1
+ and that the hole key matches 10 (duplicates) or 1
(unique) records.
Assume also that more key matches proportionally more
records
@@ -2071,6 +2075,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
records=(ulong) tmp;
}
}
+ /* Limit the number of matched rows */
+ set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
if (table->used_keys & ((key_map) 1 << key))
{
/* we can use only index tree */
@@ -2113,20 +2119,31 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
s->table->used_keys && best_key) &&
!(s->table->force_index && best_key))
{ // Check full join
+ ha_rows rnd_records= s->found_records;
if (s->on_expr)
{
- tmp=rows2double(s->found_records); // Can't use read cache
+ tmp=rows2double(rnd_records); // Can't use read cache
}
else
{
tmp=(double) s->read_time;
- /* Calculate time to read through cache */
+ /* Calculate time to read previous rows through cache */
tmp*=(1.0+floor((double) cache_record_length(join,idx)*
record_count /
(double) thd->variables.join_buff_size));
}
+
+ /*
+ If there is a restriction on the table, assume that 25% of the
+ rows can be skipped on next part.
+ This is to force tables that this table depends on before this
+ table
+ */
+ if (found_constrain)
+ rnd_records-= rnd_records/4;
+
if (best == DBL_MAX ||
- (tmp + record_count/(double) TIME_FOR_COMPARE*s->found_records <
+ (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records <
best + record_count/(double) TIME_FOR_COMPARE*records))
{
/*
@@ -2134,7 +2151,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
will ensure that this will be used
*/
best=tmp;
- records= rows2double(s->found_records);
+ records= rows2double(rnd_records);
best_key=0;
}
}
View Lorry Controller Status