diff options
| -rw-r--r-- | Docs/manual.texi | 32 | ||||
| -rw-r--r-- | mysql-test/t/alter_table.test | 20 | ||||
| -rw-r--r-- | sql/lex.h | 2 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 4 | ||||
| -rw-r--r-- | sql/sql_class.h | 1 | ||||
| -rw-r--r-- | sql/sql_lex.h | 3 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 3 | ||||
| -rw-r--r-- | sql/sql_table.cc | 66 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 37 |
9 files changed, 117 insertions, 51 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index 1472febc15e..8a6b97ec5c7 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -19115,6 +19115,8 @@ alter_specification: or DROP [COLUMN] col_name or DROP PRIMARY KEY or DROP INDEX index_name + or DISABLE KEYS + or ENABLE KEYS or RENAME [TO] new_tbl_name or ORDER BY col or table_options @@ -19277,6 +19279,15 @@ indexes are created in a separate batch (like in @code{REPAIR}). This should make @code{ALTER TABLE} much faster when you have many indexes. @item +Since @strong{MySQL 4.0} this feature could be activated explicitly. +@code{ALTER TABLE ... DISABLE KEYS} makes @strong{MySQL} to stop updating +non-unique indexes for @code{MyISAM} table. +@code{ALTER TABLE ... ENABLE KEYS} then should be used to recreate missing +indexes. As @strong{MySQL} does it with special algorithm which is much +faster then inserting keys one by one, disabling keys could give a +considerable speedup on bulk inserts. + +@item @findex mysql_info() With the C API function @code{mysql_info()}, you can find out how many records were copied, and (when @code{IGNORE} is used) how many records were @@ -24758,14 +24769,11 @@ InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... -InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c -reated +InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 -InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c -reated +InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 -InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c -reated +InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections @@ -31050,8 +31058,13 @@ Execute a @code{FLUSH TABLES} statement or the shell command @code{mysqladmin flush-tables}. @end enumerate -This procedure will be built into @code{LOAD DATA INFILE} in some future -version of @strong{MySQL}. +Since @strong{MySQL 4.0} you can also use +@code{ALTER TABLE tbl_name DISABLE KEYS} instead of +@code{myisamchk --keys-used=0 -rq /path/to/db/tbl_name} and +@code{ALTER TABLE tbl_name ENABLE KEYS} instead of +@code{myisamchk -r -q /path/to/db/tbl_name}. This way you can also skip +@code{FLUSH TABLES} steps. + @item You can speed up insertions by locking your tables: @@ -44082,6 +44095,9 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}. @itemize @bullet @item +Added @code{ALTER TABLE table_name DISABLE KEYS} and + @code{ALTER TABLE table_name ENABLE KEYS} commands. +@item Added @code{HANDLER} command. @item Added @code{SQL_CALC_FOUND_ROWS} and @code{FOUND_ROWS()}. This make it diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index da98240c2cf..25cd0de6325 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -71,3 +71,23 @@ ALTER TABLE t1 ADD Column new_col int not null; UNLOCK TABLES; OPTIMIZE TABLE t1; DROP TABLE t1; +drop table if exists t1; + +# +# ALTER TABLE ... ENABLE/DISABLE KEYS + +create table t1 (n1 int not null, n2 int, n3 int, n4 float, + unique(n1), + key (n1, n2, n3, n4), + key (n2, n3, n4, n1), + key (n3, n4, n1, n2), + key (n4, n1, n2, n3) ); +alter table t1 disable keys; +let $1=10000; +while ($1) +{ + eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); + dec $1; +} +alter table t1 enable keys; +drop table t1; diff --git a/sql/lex.h b/sql/lex.h index 0e10ff4949c..c29c4081787 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -113,6 +113,7 @@ static SYMBOL symbols[] = { { "DELETE", SYM(DELETE_SYM),0,0}, { "DESC", SYM(DESC),0,0}, { "DESCRIBE", SYM(DESCRIBE),0,0}, + { "DISABLE", SYM(DISABLE_SYM),0,0}, { "DISTINCT", SYM(DISTINCT),0,0}, { "DISTINCTROW", SYM(DISTINCT),0,0}, /* Access likes this */ { "DOUBLE", SYM(DOUBLE_SYM),0,0}, @@ -123,6 +124,7 @@ static SYMBOL symbols[] = { { "ELSE", SYM(ELSE),0,0}, { "ESCAPE", SYM(ESCAPE_SYM),0,0}, { "ESCAPED", SYM(ESCAPED),0,0}, + { "ENABLE", SYM(ENABLE_SYM),0,0}, { "ENCLOSED", SYM(ENCLOSED),0,0}, { "ENUM", SYM(ENUM),0,0}, { "EXPLAIN", SYM(DESCRIBE),0,0}, diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 3fdc50b1521..148cc8d528d 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -328,7 +328,9 @@ int mysql_alter_table(THD *thd, char *new_db, char *new_name, List<Alter_column> &alter_list, ORDER *order, bool drop_primary, - enum enum_duplicates handle_duplicates); + enum enum_duplicates handle_duplicates, + enum enum_enable_or_disable keys_onoff=LEAVE_AS_IS, + bool simple_alter=0); bool mysql_rename_table(enum db_type base, const char *old_db, const char * old_name, diff --git a/sql/sql_class.h b/sql/sql_class.h index ac6c50d0166..a67c0d13682 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -24,6 +24,7 @@ class Query_log_event; class Load_log_event; +enum enum_enable_or_disable { LEAVE_AS_IS, ENABLE, DISABLE }; enum enum_ha_read_modes { RFIRST, RNEXT, RPREV, RLAST, RKEY }; enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_IGNORE }; enum enum_log_type { LOG_CLOSED, LOG_NORMAL, LOG_NEW, LOG_BIN }; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f505b870d67..0df5bbebc37 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -144,10 +144,11 @@ typedef struct st_lex { enum enum_tx_isolation tx_isolation; enum enum_ha_read_modes ha_read_mode; enum ha_rkey_function ha_rkey_mode; + enum enum_enable_or_disable alter_keys_onoff; uint in_sum_expr,grant,grant_tot_col,which_columns, sort_default; thr_lock_type lock_option; bool create_refs,drop_primary,drop_if_exists,local_file; - bool in_comment,ignore_space,verbose; + bool in_comment,ignore_space,verbose,simple_alter; } LEX; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 916af73acae..3dda8d1cff7 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1393,7 +1393,8 @@ mysql_execute_command(void) tables, lex->create_list, lex->key_list, lex->drop_list, lex->alter_list, (ORDER *) lex->order_list.first, - lex->drop_primary, lex->duplicates); + lex->drop_primary, lex->duplicates, + lex->alter_keys_onoff, lex->simple_alter); break; } #endif diff --git a/sql/sql_table.cc b/sql/sql_table.cc index ad39b91a5ca..89699655ab9 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -1084,7 +1084,9 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, List<Alter_column> &alter_list, ORDER *order, bool drop_primary, - enum enum_duplicates handle_duplicates) + enum enum_duplicates handle_duplicates, + enum enum_enable_or_disable keys_onoff, + bool simple_alter) { TABLE *table,*new_table; int error; @@ -1149,39 +1151,50 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, if (create_info->row_type == ROW_TYPE_DEFAULT) create_info->row_type=table->row_type; - /* Check if the user only wants to do a simple RENAME */ + /* In some simple cases we need not to recreate the table */ thd->proc_info="setup"; - if (new_name != table_name && - !fields.elements && !keys.elements && ! drop_list.elements && - !alter_list.elements && !drop_primary && - new_db_type == old_db_type && create_info->max_rows == 0 && - create_info->auto_increment_value == 0 && !table->tmp_table) + if (simple_alter) { - thd->proc_info="rename"; - VOID(pthread_mutex_lock(&LOCK_open)); - /* Then do a 'simple' rename of the table */ error=0; - if (!access(new_name_buff,F_OK)) - { - my_error(ER_TABLE_EXISTS_ERROR,MYF(0),new_name); - error= -1; - } - else + if (new_name != table_name) { - *fn_ext(new_name)=0; - close_cached_table(thd,table); - if (mysql_rename_table(old_db_type,db,table_name,new_db,new_name)) - error= -1; + thd->proc_info="rename"; + VOID(pthread_mutex_lock(&LOCK_open)); + /* Then do a 'simple' rename of the table */ + error=0; + if (!access(new_name_buff,F_OK)) + { + my_error(ER_TABLE_EXISTS_ERROR,MYF(0),new_name); + error= -1; + } + else + { + *fn_ext(new_name)=0; + close_cached_table(thd,table); + if (mysql_rename_table(old_db_type,db,table_name,new_db,new_name)) + error= -1; + } + if (!error && (error=ha_commit_rename(thd))) + { + my_error(ER_GET_ERRNO,MYF(0),error); + error=1; + } + + VOID(pthread_cond_broadcast(&COND_refresh)); + VOID(pthread_mutex_unlock(&LOCK_open)); } - if (!error && (error=ha_commit_rename(thd))) + if (!error) { - my_error(ER_GET_ERRNO,MYF(0),error); - error=1; + switch (keys_onoff) + { + case LEAVE_AS_IS: break; + case ENABLE: error=table->file->activate_all_index(thd); break; + case DISABLE: + table->file->deactivate_non_unique_index(table->file->records); + break; + } } - - VOID(pthread_cond_broadcast(&COND_refresh)); - VOID(pthread_mutex_unlock(&LOCK_open)); if (!error) { mysql_update_log.write(thd, thd->query, thd->query_length); @@ -1192,7 +1205,6 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, } send_ok(&thd->net); } - DBUG_RETURN(error); } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 8485f08d23d..172fb0830fe 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -162,7 +162,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token DESC %token DESCRIBE %token DISTINCT +%token DISABLE_SYM %token DYNAMIC_SYM +%token ENABLE_SYM %token ENCLOSED %token ESCAPED %token ESCAPE_SYM @@ -1066,6 +1068,8 @@ alter: lex->db=lex->name=0; bzero((char*) &lex->create_info,sizeof(lex->create_info)); lex->create_info.db_type= DB_TYPE_DEFAULT; + lex->alter_keys_onoff=LEAVE_AS_IS; + lex->simple_alter=1; } alter_list @@ -1074,16 +1078,18 @@ alter_list: | alter_list ',' alter_list_item add_column: - ADD opt_column { Lex->change=0;} + ADD opt_column { Lex->change=0; } alter_list_item: - add_column field_list_item opt_place - | add_column '(' field_list ')' - | CHANGE opt_column field_ident { Lex->change= $3.str; } field_spec + add_column field_list_item opt_place { Lex->simple_alter=0; } + | add_column '(' field_list ')' { Lex->simple_alter=0; } + | CHANGE opt_column field_ident { Lex->change= $3.str; Lex->simple_alter=0; } + field_spec | MODIFY_SYM opt_column field_ident { Lex->length=Lex->dec=0; Lex->type=0; Lex->interval=0; Lex->default_value=0; + Lex->simple_alter=0; } type opt_attribute { @@ -1093,23 +1099,26 @@ alter_list_item: Lex->default_value, $3.str, Lex->interval)) YYABORT; + Lex->simple_alter=0; } | DROP opt_column field_ident opt_restrict { Lex->drop_list.push_back(new Alter_drop(Alter_drop::COLUMN, - $3.str)); } - | DROP PRIMARY_SYM KEY_SYM { Lex->drop_primary=1; } - | DROP FOREIGN KEY_SYM opt_ident {} + $3.str)); Lex->simple_alter=0; } + | DROP PRIMARY_SYM KEY_SYM { Lex->drop_primary=1; Lex->simple_alter=0; } + | DROP FOREIGN KEY_SYM opt_ident { Lex->simple_alter=0; } | DROP key_or_index field_ident { Lex->drop_list.push_back(new Alter_drop(Alter_drop::KEY, - $3.str)); } + $3.str)); Lex->simple_alter=0; } + | DISABLE_SYM KEYS { Lex->alter_keys_onoff=DISABLE; } + | ENABLE_SYM KEYS { Lex->alter_keys_onoff=ENABLE; } | ALTER opt_column field_ident SET DEFAULT literal - { Lex->alter_list.push_back(new Alter_column($3.str,$6)); } + { Lex->alter_list.push_back(new Alter_column($3.str,$6)); Lex->simple_alter=0; } | ALTER opt_column field_ident DROP DEFAULT - { Lex->alter_list.push_back(new Alter_column($3.str,(Item*) 0)); } + { Lex->alter_list.push_back(new Alter_column($3.str,(Item*) 0)); Lex->simple_alter=0; } | RENAME opt_to table_alias table_ident - { Lex->db=$4->db.str ; Lex->name= $4->table.str; } - | create_table_options - | order_clause + { Lex->db=$4->db.str ; Lex->name= $4->table.str; Lex->simple_alter=0; } + | create_table_options { Lex->simple_alter=0; } + | order_clause { Lex->simple_alter=0; } opt_column: /* empty */ {} @@ -2567,8 +2576,10 @@ keyword: | DATE_SYM {} | DAY_SYM {} | DELAY_KEY_WRITE_SYM {} + | DISABLE_SYM {} | DUMPFILE {} | DYNAMIC_SYM {} + | ENABLE_SYM {} | END {} | ENUM {} | ESCAPE_SYM {} |