List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 29 1999 5:26pm
Subject:Re: simple alter tables take a long time
View as plain text  
Mike Wexler wrote:
> 
> I have a table with about 500,000 records
> 
> show fields from inventory;
> +----------------+--------------+------+-----+---------------------+-------+
> | Field          | Type         | Null | Key | Default             | Extra |
> +----------------+--------------+------+-----+---------------------+-------+
> | storeid        | varchar(64)  |      | PRI |                     |       |
> | catId          | varchar(64)  |      | PRI |                     |       |
> | subCategory    | varchar(128) | YES  |     | NULL                |       |
> | status         | varchar(64)  | YES  |     | NULL                |       |
> | type           | varchar(64)  | YES  |     | NULL                |       |
> | description    | text         |      |     | NULL                |       |
> | itemNo         | varchar(64)  |      | PRI |                     |       |
> | size           | varchar(32)  | YES  |     | NULL                |       |
> | qty            | int(11)      | YES  |     | NULL                |       |
> | priceString    | varchar(32)  | YES  |     | NULL                |       |
> | imageFile      | varchar(255) | YES  |     | NULL                |       |
> | title          | varchar(128) | YES  |     | NULL                |       |
> | subTitle       | varchar(128) | YES  |     | NULL                |       |
> | author         | varchar(128) | YES  |     | NULL                |       |
> | pages          | varchar(32)  | YES  |     | NULL                |       |
> | isbn           | varchar(32)  | YES  |     | NULL                |       |
> | publisher      | varchar(128) | YES  |     | NULL                |       |
> | year           | varchar(32)  | YES  |     | NULL                |       |
> | edition        | varchar(32)  | YES  |     | NULL                |       |
> | enteredStamp   | datetime     |      | MUL | 0000-00-00 00:00:00 |       |
> | updatedStamp   | datetime     |      | MUL | 0000-00-00 00:00:00 |       |
> | condition      | varchar(32)  | YES  |     | NULL                |       |
> | isNew          | tinyint(4)   | YES  |     | NULL                |       |
> | weight         | varchar(32)  | YES  |     | NULL                |       |
> | color          | varchar(64)  | YES  |     | NULL                |       |
> | systemCategory | varchar(128) |      | MUL |                     |       |
> | seqNo          | int(4)       | YES  |     | NULL                |       |
> | price          | decimal(9,2) | YES  |     | NULL                |       |
> | unitMeasure    | varchar(15)  | YES  |     | NULL                |       |
> +----------------+--------------+------+-----+---------------------+-------+
> 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?
> 
> mysqladmin  Ver 7.8 Distrib 3.22.20a, for pc-linux-gnu on i686
> TCX Datakonsult AB, by Monty
> 
> Server version          3.22.20a
> Protocol version        10
> Connection              Localhost via UNIX socket
> 
> The machine is a 400 Mhz Pentium II with 512 Megabytes of RAM, 2 Fast+Wide
> SCSI disks and RedHat Linux 5.1.
> 

MySQL alters table by creating a temporary table, moving the data over
with alterations, unlinking the old, and renaming the temp. It does not,
as far as I know, perform any checking if this actually needs to be
done. So whenever you alter table it will still go through the whole
nine yards even if this is just the change of spelling - not very
efficient, but how often does one alter tables?

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
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