The ALTER TABLE is going to "copy" the entire table when it executes the
ALTER TABLE so it will take some time. Depends on your server, diaks,
table type etc..
One alternative might be to do a
SELECT a, enumcolumn INTO OUTFILE 'x' FROM tablename;
TRUNCATE tablename;
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c')
DEFAULT "a" NOT NULL;
LOAD DATA INFILE 'x' INTO TABLE tablename;
I know this seems obtuse, but load data infile and select into outfile
seem to run very fast and for what ever reason may just be faster than
the ALTER TABLE on the fully populated table.
-----Original Message-----
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 1:18 PM
To: mysql@lists.mysql.com
Subject: Re: alter only an enum label
hum... clever. i liked that solution.
but do have experience on how long it will take with milions of records?
all records havin only a int(11) as unique key and the enum field..
suposing now i have enum("a", "b") only, and did a "ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');"
thanks,
Gabriel
2005/6/16, Gordon Bruce <[EMAIL PROTECTED]>:
> If you have "c" values in the table currently you can just do an
>
> ALTER TABLE tablename CHANGE columnname columnname
ENUM('a','b','c','x')
> DEFAULT "a" NOT NULL
>
> then
>
> UPDATE tablename SET columname = 'x' WHERE columname = 'c'
>
> Then
>
> ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
> DEFAULT "a" NOT NULL
>
> -----Original Message-----
> From: Gabriel B. [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 16, 2005 12:54 PM
> To: LISTA mysql
> Subject: alter only an enum label
>
> If i have a table with about 800M records. and one of the fields is a
> enum("a", "b", "c") and i want to change it to enum("a","b","x") will
> it fall into some optimization and be instant?
>
> and what if previously i've never used the "c" value? isn't there any
> optimization for that? ...leaving "blank" labels on a enum? or another
> command to "add" new labels to a enum?
>
> thanks,
> Gabriel
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
