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)
