List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 21 2001 12:14pm
Subject:Possible ALTER TABLE Modification
View as plain text  

>>>>> "Michael" == Michael Champlain <mike@stripped> writes:

Michael> Hi All !

Michael> I noticed something I considered to be a useful change to the MySQL
Michael> ALTER TABLE algorithm and wanted to share it with you. After scanning
Michael> the descriptions of the mailing lists on, I believe this is the
Michael> correct list. If not, my apologies.

Michael> I was working on a database table and needed to change a field name.
Michael> The table is rather large, approx 55 million records, and being the fine
Michael> product MySQL is, I assumed that issueing the following command:

Michael>      alter table addresses change field1 state char(2) not null;

Michael> would change only the name of the field, since its size/type/flags werent
Michael> being modified, without requireing a complete table scan. Much to my
Michael> surprise, after I issued this command, MySQL started to create a complete
Michael> set of temporary files and took approximately 40 minutes, just to change the
Michael> name of the field.

ALTER TABLE will always (except when doing RENAME) create a copy of
the table. The reason for this is simply that we haven't had time to
optimize this case. This hasn't also been that important as the
current version works ok in most cases and most people doesn't do
ALTER TABLE that often.


Possible ALTER TABLE ModificationMichael Champlain20 Mar
  • Possible ALTER TABLE ModificationMichael Widenius21 Mar