MySQL Bugs: #60810: some optimizations at alter table

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:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, qc

Description:
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

maybe...
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)

changed to ddl

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.

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...
check:

1) ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
2) ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
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...