List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 2 1999 7:02pm
Subject:RE: simple alter tables take a long time
View as plain text  
>>>>> "jim" == jim cistaro <jim.cistaro@stripped> writes:

jim> Mike Wexler writes:
>> I have a table with about 500,000 records
<cut>
>> 29 rows in set (0.00 sec)
>> 
>> When I do a
>> 
mysql> alter table inventory change column storeid storeId varchar(64) NOT 
>> NULL;
>> Query OK, 539321 rows affected (9 min 21.22 sec)
>> Records: 539321  Duplicates: 0  Warnings: 0
>> 
>> It takes over nine minutes. Why does changing the spelling of a field name 
>> take so long? Shouldn't it be able to update the schema almost instantly? 
>> Does it need to touch all the records for some reason?

Sorry;  We haven't had time to add optimization for this case.

jim> just a thought but if you had to do this on a larger table where the
jim> time was prohibitive.  you could create a table with identical
jim> properties and no rows, alter that table and copy the .frm, could you
jim> not.

Yes.

jim> a risky shortcut which should only be taken on name changes, but isn't 
jim> the name only stored in the frm file? ???

Yes.  As long as you don't do anything that affects how columns are
stored this is safe.  You must of course do 'mysqladmin flush-tables'
to tell MySQL to re-read the .frm file.

jim> if anyone can authoritatively state whether this would work, i would
jim> appreciate it for further reference.

Regards,
Monty
Thread
simple alter tables take a long timeMike Wexler29 Jul
  • Re: simple alter tables take a long timeSasha Pachev29 Jul
  • RE: simple alter tables take a long timejim.cistaro29 Jul
    • RE: simple alter tables take a long timeMichael Widenius2 Aug