Bug #60810 some optimizations at alter table
Submitted: 8 Apr 2011 21:07 Modified: 26 May 2011 1:34
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.1 OS:Any (any)
Assigned to: CPU Architecture:Any

[8 Apr 2011 21:07] Roberto Spadim
some optimizations to alter table...

ALTER TABLE `mov_contacorrente`  CHANGE COLUMN `recompra_Valor` `recompra_valor` DECIMAL(17,5) NOT NULL DEFAULT '0.00000' AFTER `credor_ca_impret`;

this one should only change field name -> change .frm file only
but mysql made a repair table?!

some fields that don´t need change at data/index could be optimized too
for example....

changing a field of tinyint unsigned, to tinyint signed with not values >125 could be optimized to only change .frm file

maybe some optimizations at engines / mysql server could allow this types of optimizations

change name / change fieds from unsigned to signed, zerofill/nonzerofill and others alters that don´t need a table repair/reorder

How to repeat:
create any table with many data 10000000rows
change the name of one field
for example

create table t (a int not null, primary key (a))

populate t

alter table t change a A int not null;

Suggested fix:
maybe it´s a new function before send alter table to engines (myisam/innodb)

they should check if data will be changed, if not only change .frm file

another good example...

create table t (a int not null);
insert into t (1);
insert into t (2);
insert into t (3);
insert into t (1000000);

alter table t change a a int not null unsigned;

no data should be changed if internally (10000000 integer)=(10000000 unsigned integer) - this is a myisam/innodb/engine check

this could speed up a lot alters
[8 Apr 2011 21:55] Roberto Spadim
instead of

alter table t change a A int not null

use something like:

alter table t rename a A

this don´t need engine code changes (maybe if some engine use field for hash?! i don´t know if engine have case insensitive functions to make hash index or another kind of optimization, verification)
[8 Apr 2011 22:01] Roberto Spadim
changed to ddl
[11 Apr 2011 14:02] Valeriy Kravchuk
According to http://dev.mysql.com/doc/refman/5.5/en/alter-table.html renaming column should work without making a temporary table and copying all the data. 

Other cases discussed here (and renaming column of InnoDB table) are, probably, valid cases for some optimization.
[26 May 2011 1:34] Roberto Spadim
i was reading http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
5.6 docs
and i didn´t found a command to change column name...

1) ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
3) CHANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name]
4) MODIFY [COLUMN] col_name column_definition    [FIRST | AFTER col_name]
5) DROP [COLUMN] col_name

but no command to rename column.. only change can rename
may be 

a new 
6) RENAME COLUMN old_col_name new_col_name

could be nice...