summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/range.result133
-rw-r--r--mysql-test/t/range.test107
-rw-r--r--sql/handler.cc71
-rw-r--r--sql/handler.h7
-rw-r--r--sql/mysql_priv.h8
-rw-r--r--sql/opt_range.cc9
-rw-r--r--sql/set_var.h72
7 files changed, 370 insertions, 37 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 9f76676ee1a..91b718f9121 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1,4 +1,4 @@
-drop table if exists t1;
+drop table if exists t1, t2;
CREATE TABLE t1 (
event_date date DEFAULT '0000-00-00' NOT NULL,
type int(11) DEFAULT '0' NOT NULL,
@@ -286,3 +286,134 @@ id
5
9
drop table t1;
+create table t1 (
+id int not null auto_increment,
+name char(1) not null,
+uid int not null,
+primary key (id),
+index uid_index (uid));
+create table t2 (
+id int not null auto_increment,
+name char(1) not null,
+uid int not null,
+primary key (id),
+index uid_index (uid));
+insert into t1(id, uid, name) values(1, 0, ' ');
+insert into t1(uid, name) values(0, ' ');
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t2(uid, name) select uid, name from t1;
+insert into t2(uid, name) select uid, name from t1;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+delete from t2;
+insert into t2(uid, name) values
+(1, CHAR(64+1)),
+(2, CHAR(64+2)),
+(3, CHAR(64+3)),
+(4, CHAR(64+4)),
+(5, CHAR(64+5)),
+(6, CHAR(64+6)),
+(7, CHAR(64+7)),
+(8, CHAR(64+8)),
+(9, CHAR(64+9)),
+(10, CHAR(64+10)),
+(11, CHAR(64+11)),
+(12, CHAR(64+12)),
+(13, CHAR(64+13)),
+(14, CHAR(64+14)),
+(15, CHAR(64+15)),
+(16, CHAR(64+16)),
+(17, CHAR(64+17)),
+(18, CHAR(64+18)),
+(19, CHAR(64+19)),
+(20, CHAR(64+20)),
+(21, CHAR(64+21)),
+(22, CHAR(64+22)),
+(23, CHAR(64+23)),
+(24, CHAR(64+24)),
+(25, CHAR(64+25)),
+(26, CHAR(64+26));
+insert into t1(uid, name) select uid, name from t2;
+delete from t2;
+insert into t2(id, uid, name) select id, uid, name from t1;
+select count(*) from t1;
+count(*)
+1026
+select count(*) from t2;
+count(*)
+1026
+explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range uid_index uid_index 4 NULL 128 Using where
+1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
+explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range uid_index uid_index 4 NULL 129 Using where
+1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid 38
+select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
+id name uid id name uid
+1001 A 1 1001 A 1
+1002 B 2 1002 B 2
+1003 C 3 1003 C 3
+1004 D 4 1004 D 4
+1005 E 5 1005 E 5
+1006 F 6 1006 F 6
+1007 G 7 1007 G 7
+1008 H 8 1008 H 8
+1009 I 9 1009 I 9
+1010 J 10 1010 J 10
+1011 K 11 1011 K 11
+1012 L 12 1012 L 12
+1013 M 13 1013 M 13
+1014 N 14 1014 N 14
+1015 O 15 1015 O 15
+1016 P 16 1016 P 16
+1017 Q 17 1017 Q 17
+1018 R 18 1018 R 18
+1019 S 19 1019 S 19
+1020 T 20 1020 T 20
+1021 U 21 1021 U 21
+1022 V 22 1022 V 22
+1023 W 23 1023 W 23
+1024 X 24 1024 X 24
+1025 Y 25 1025 Y 25
+1026 Z 26 1026 Z 26
+select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
+id name uid id name uid
+1001 A 1 1001 A 1
+1002 B 2 1002 B 2
+1003 C 3 1003 C 3
+1004 D 4 1004 D 4
+1005 E 5 1005 E 5
+1006 F 6 1006 F 6
+1007 G 7 1007 G 7
+1008 H 8 1008 H 8
+1009 I 9 1009 I 9
+1010 J 10 1010 J 10
+1011 K 11 1011 K 11
+1012 L 12 1012 L 12
+1013 M 13 1013 M 13
+1014 N 14 1014 N 14
+1015 O 15 1015 O 15
+1016 P 16 1016 P 16
+1017 Q 17 1017 Q 17
+1018 R 18 1018 R 18
+1019 S 19 1019 S 19
+1020 T 20 1020 T 20
+1021 U 21 1021 U 21
+1022 V 22 1022 V 22
+1023 W 23 1023 W 23
+1024 X 24 1024 X 24
+1025 Y 25 1025 Y 25
+1026 Z 26 1026 Z 26
+drop table t1,t2;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 7bf6570b558..40ae49f8005 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -3,7 +3,7 @@
#
--disable_warnings
-drop table if exists t1;
+drop table if exists t1, t2;
--enable_warnings
CREATE TABLE t1 (
@@ -174,18 +174,6 @@ select count(*) from t1 where art = 'j' or art = 'J';
select count(*) from t1 where art = 'j';
select count(*) from t1 where art = 'J';
drop table t1;
-
-create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2));
-insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"),
- (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"),
- (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"),
- (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"),
- (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"),
- (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"),
- (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa");
-select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1;
-drop table t1;
-
#
# BETWEEN problems
#
@@ -225,3 +213,96 @@ insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
select id from t1 where id in (2,5,9) ;
select id from t1 where id=2 or id=5 or id=9 ;
drop table t1;
+create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2));
+insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"),
+ (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"),
+ (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"),
+ (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"),
+ (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"),
+ (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"),
+ (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa");
+select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1;
+drop table t1;
+
+
+#
+# Problem with optimizing !=
+#
+
+create table t1 (
+ id int not null auto_increment,
+ name char(1) not null,
+ uid int not null,
+ primary key (id),
+ index uid_index (uid));
+
+create table t2 (
+ id int not null auto_increment,
+ name char(1) not null,
+ uid int not null,
+ primary key (id),
+ index uid_index (uid));
+
+insert into t1(id, uid, name) values(1, 0, ' ');
+insert into t1(uid, name) values(0, ' ');
+
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+insert into t2(uid, name) select uid, name from t1;
+insert into t2(uid, name) select uid, name from t1;
+insert into t2(uid, name) select uid, name from t1;
+insert into t2(uid, name) select uid, name from t1;
+insert into t1(uid, name) select uid, name from t2;
+
+delete from t2;
+insert into t2(uid, name) values
+ (1, CHAR(64+1)),
+ (2, CHAR(64+2)),
+ (3, CHAR(64+3)),
+ (4, CHAR(64+4)),
+ (5, CHAR(64+5)),
+ (6, CHAR(64+6)),
+ (7, CHAR(64+7)),
+ (8, CHAR(64+8)),
+ (9, CHAR(64+9)),
+ (10, CHAR(64+10)),
+ (11, CHAR(64+11)),
+ (12, CHAR(64+12)),
+ (13, CHAR(64+13)),
+ (14, CHAR(64+14)),
+ (15, CHAR(64+15)),
+ (16, CHAR(64+16)),
+ (17, CHAR(64+17)),
+ (18, CHAR(64+18)),
+ (19, CHAR(64+19)),
+ (20, CHAR(64+20)),
+ (21, CHAR(64+21)),
+ (22, CHAR(64+22)),
+ (23, CHAR(64+23)),
+ (24, CHAR(64+24)),
+ (25, CHAR(64+25)),
+ (26, CHAR(64+26));
+
+insert into t1(uid, name) select uid, name from t2;
+
+delete from t2;
+insert into t2(id, uid, name) select id, uid, name from t1;
+
+select count(*) from t1;
+select count(*) from t2;
+
+explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
+explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
+
+select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
+select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
+
+drop table t1,t2;
diff --git a/sql/handler.cc b/sql/handler.cc
index e14e326792d..1d12171cbd0 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -739,6 +739,11 @@ int handler::analyze(THD* thd, HA_CHECK_OPT* check_opt)
return HA_ADMIN_NOT_IMPLEMENTED;
}
+int handler::assign_to_keycache(THD* thd, HA_CHECK_OPT* check_opt)
+{
+ return HA_ADMIN_NOT_IMPLEMENTED;
+}
+
int handler::preload_keys(THD* thd, HA_CHECK_OPT* check_opt)
{
return HA_ADMIN_NOT_IMPLEMENTED;
@@ -1105,27 +1110,61 @@ int ha_create_table(const char *name, HA_CREATE_INFO *create_info,
/* Use key cacheing on all databases */
-void ha_key_cache(void)
+int ha_key_cache(KEY_CACHE_VAR *key_cache)
{
- /*
- The following mutex is not really needed as long as keybuff_size is
- treated as a long value, but we use the mutex here to guard for future
- changes.
- */
- pthread_mutex_lock(&LOCK_global_system_variables);
- long tmp= (long) keybuff_size;
- pthread_mutex_unlock(&LOCK_global_system_variables);
- if (tmp)
- (void) init_key_cache(tmp);
+ if (!key_cache->cache)
+ {
+ /*
+ The following mutex is not really needed as long as keybuff_size is
+ treated as a long value, but we use the mutex here to guard for future
+ changes.
+ */
+ pthread_mutex_lock(&LOCK_global_system_variables);
+ if (!key_cache->block_size)
+ key_cache->block_size= dflt_key_cache_block_size;
+ if (!key_cache->buff_size)
+ key_cache->buff_size= dflt_key_buff_size;
+ long tmp_buff_size= (long) key_cache->buff_size;
+ long tmp_block_size= (long) key_cache->block_size;
+ pthread_mutex_unlock(&LOCK_global_system_variables);
+ return !init_key_cache(&key_cache->cache,
+ tmp_block_size,
+ tmp_buff_size,
+ key_cache);
+ }
+ return 0;
}
-void ha_resize_key_cache(void)
+ if (key_cache->cache)
+ {
+ pthread_mutex_lock(&LOCK_global_system_variables);
+ long tmp_buff_size= (long) key_cache->buff_size;
+ long tmp_block_size= (long) key_cache->block_size;
+ pthread_mutex_unlock(&LOCK_global_system_variables);
+ return !resize_key_cache(&key_cache->cache, tmp_block_size,
+ tmp_buff_size);
+ }
+ return 0;
+}
+
+int ha_change_key_cache_param(KEY_CACHE_VAR *key_cache)
+{
+ if (key_cache->cache)
+ {
+ change_key_cache_param(key_cache->cache);
+ }
+ return 0;
+}
+
+int ha_end_key_cache(KEY_CACHE_VAR *key_cache)
{
- pthread_mutex_lock(&LOCK_global_system_variables);
- long tmp= (long) keybuff_size;
- pthread_mutex_unlock(&LOCK_global_system_variables);
- (void) resize_key_cache(tmp);
+ if (key_cache->cache)
+ {
+ end_key_cache(&key_cache->cache, 1);
+ return key_cache->cache ? 1 : 0;
+ }
+ return 0;
}
diff --git a/sql/handler.h b/sql/handler.h
index b74e06c6edf..1fcc0da24f8 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -307,6 +307,7 @@ public:
virtual bool check_and_repair(THD *thd) {return 1;}
virtual int optimize(THD* thd,HA_CHECK_OPT* check_opt);
virtual int analyze(THD* thd, HA_CHECK_OPT* check_opt);
+ virtual int assign_to_keycache(THD* thd, HA_CHECK_OPT* check_opt);
virtual int preload_keys(THD* thd, HA_CHECK_OPT* check_opt);
virtual int backup(THD* thd, HA_CHECK_OPT* check_opt);
/*
@@ -389,8 +390,10 @@ int ha_create_table(const char *name, HA_CREATE_INFO *create_info,
bool update_create_info);
int ha_delete_table(enum db_type db_type, const char *path);
void ha_drop_database(char* path);
-void ha_key_cache(void);
-void ha_resize_key_cache(void);
+int ha_key_cache(KEY_CACHE_VAR *key_cache);
+int ha_resize_key_cache(KEY_CACHE_VAR *key_cache);
+int ha_change_key_cache_param(KEY_CACHE_VAR *key_cache);
+int ha_end_key_cache(KEY_CACHE_VAR *key_cache);
int ha_start_stmt(THD *thd);
int ha_report_binlog_offset_and_commit(THD *thd, char *log_file_name,
my_off_t end_offset);
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 06cee8018f8..4254d01f903 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -396,6 +396,10 @@ bool check_stack_overrun(THD *thd,char *dummy);
void table_cache_init(void);
void table_cache_free(void);
uint cached_tables(void);
+void assign_cache_init(void);
+void assign_cache_free(void);
+void reassign_key_cache(KEY_CACHE_ASMT *key_cache_asmt,
+ KEY_CACHE_VAR *new_key_cache);
void kill_mysql(void);
void close_connection(THD *thd, uint errcode, bool lock);
bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables,
@@ -425,7 +429,10 @@ int mysql_analyze_table(THD* thd, TABLE_LIST* table_list,
HA_CHECK_OPT* check_opt);
int mysql_optimize_table(THD* thd, TABLE_LIST* table_list,
HA_CHECK_OPT* check_opt);
+int mysql_assign_to_keycache(THD* thd, TABLE_LIST* table_list);
int mysql_preload_keys(THD* thd, TABLE_LIST* table_list);
+int reassign_keycache_tables(THD* thd, KEY_CACHE_VAR* src_cache,
+ char *dest_name, bool remove_fl);
bool check_simple_select();
@@ -797,6 +804,7 @@ extern pthread_mutex_t LOCK_mysql_create_db,LOCK_Acl,LOCK_open,
LOCK_delayed_status, LOCK_delayed_create, LOCK_crypt, LOCK_timezone,
LOCK_slave_list, LOCK_active_mi, LOCK_manager,
LOCK_global_system_variables, LOCK_user_conn;
+ LOCK_global_system_variables, LOCK_assign;
extern rw_lock_t LOCK_grant;
extern pthread_cond_t COND_refresh, COND_thread_count, COND_manager;
extern pthread_attr_t connection_attrib;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 5b1e2c98001..7376c2e24bb 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -890,6 +890,7 @@ static SEL_TREE *
get_mm_parts(PARAM *param, Field *field, Item_func::Functype type,
Item *value, Item_result cmp_type)
{
+ bool ne_func= FALSE;
DBUG_ENTER("get_mm_parts");
if (field->table != param->table)
DBUG_RETURN(0);
@@ -927,6 +928,14 @@ get_mm_parts(PARAM *param, Field *field, Item_func::Functype type,
tree->keys[key_part->key]=sel_add(tree->keys[key_part->key],sel_arg);
}
}
+
+ if (ne_func)
+ {
+ SEL_TREE *tree2= get_mm_parts(param, field, Item_func::GT_FUNC,
+ value, cmp_type);
+ if (tree2)
+ tree= tree=tree_or(param,tree,tree2);
+ }
DBUG_RETURN(tree);
}
diff --git a/sql/set_var.h b/sql/set_var.h
index 812bd6c9420..76db478c50d 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -30,6 +30,11 @@ class set_var;
typedef struct system_variables SV;
extern TYPELIB bool_typelib, delay_key_write_typelib, sql_mode_typelib;
+extern ulonglong dflt_key_buff_size;
+extern uint dflt_key_cache_block_size;
+extern uint dflt_key_cache_division_limit;
+extern uint dflt_key_cache_age_threshold;
+
enum enum_var_type
{
OPT_DEFAULT, OPT_SESSION, OPT_GLOBAL
@@ -541,15 +546,71 @@ public:
byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
};
-class sys_var_key_buffer_size :public sys_var
+class sys_var_key_cache_param :public sys_var
{
+protected:
+ uint offset;
public:
- sys_var_key_buffer_size(const char *name_arg)
+ sys_var_key_cache_param(const char *name_arg)
:sys_var(name_arg)
- {}
+ {
+ offset= 0;
+ }
+ byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+};
+
+class sys_var_key_buffer_size :public sys_var_key_cache_param
+{
+public:
+ sys_var_key_buffer_size(const char *name_arg)
+ :sys_var_key_cache_param(name_arg)
+ {
+ offset= offsetof(KEY_CACHE_VAR, buff_size);
+ }
bool update(THD *thd, set_var *var);
SHOW_TYPE type() { return SHOW_LONGLONG; }
- byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+ bool check_default(enum_var_type type) { return 1; }
+ bool is_struct() { return 1; }
+};
+
+class sys_var_key_cache_block_size :public sys_var_key_cache_param
+{
+public:
+ sys_var_key_cache_block_size(const char *name_arg)
+ :sys_var_key_cache_param(name_arg)
+ {
+ offset= offsetof(KEY_CACHE_VAR, block_size);
+ }
+ bool update(THD *thd, set_var *var);
+ SHOW_TYPE type() { return SHOW_LONG; }
+ bool check_default(enum_var_type type) { return 1; }
+ bool is_struct() { return 1; }
+};
+
+class sys_var_key_cache_division_limit :public sys_var_key_cache_param
+{
+public:
+ sys_var_key_cache_division_limit(const char *name_arg)
+ :sys_var_key_cache_param(name_arg)
+ {
+ offset= offsetof(KEY_CACHE_VAR, division_limit);
+ }
+ bool update(THD *thd, set_var *var);
+ SHOW_TYPE type() { return SHOW_LONG; }
+ bool check_default(enum_var_type type) { return 1; }
+ bool is_struct() { return 1; }
+};
+
+class sys_var_key_cache_age_threshold :public sys_var_key_cache_param
+{
+public:
+ sys_var_key_cache_age_threshold(const char *name_arg)
+ :sys_var_key_cache_param(name_arg)
+ {
+ offset= offsetof(KEY_CACHE_VAR, age_threshold);
+ }
+ bool update(THD *thd, set_var *var);
+ SHOW_TYPE type() { return SHOW_LONG; }
bool check_default(enum_var_type type) { return 1; }
bool is_struct() { return 1; }
};
@@ -734,5 +795,6 @@ gptr find_named(I_List<NAMED_LIST> *list, const char *name, uint length,
void delete_elements(I_List<NAMED_LIST> *list, void (*free_element)(gptr));
/* key_cache functions */
-KEY_CACHE *get_or_create_key_cache(const char *name, uint length);
+KEY_CACHE_VAR *get_or_create_key_cache(const char *name, uint length);
void free_key_cache(gptr key_cache);
+bool process_key_caches(int (* func) (KEY_CACHE_VAR *));
View Lorry Controller Status