Re: Alter Table Add Column - How Long to update
On Sun, 2006-10-22 at 21:32 +0800, 黄小聪 wrote: > "it says this is done so that other read processes can still > access the DB/table in it's OLD state w/o any hiccups." > I do not understand > so how does the MSSQL DB work when we alter table add column Frankly, I don't really know. I tried adding a new column to an existing MSSQL DB and it really just took a few seconds. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
"it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups." I do not understand so how does the MSSQL DB work when we alter table add column* * 2006/10/22, Ow Mun Heng <[EMAIL PROTECTED]>: On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: > On Thu, October 19, 2006 18:24, Ow Mun Heng said: > > Just curious to know, > > > > I tried to update a table with ~1.7 million rows (~1G in size) and the > > update took close to 15-20 minutes before it says it's done. > > Is this kind of speed expected? > > > > I don't really understand how the alter table add column is done, but > > when I look at the "show processlist" I see that it says the state is > > "copying into tmp table" > > > > Does the alter table mean that MySQL has to copy the Entire table, > > row-by-row into a temporary table, and add in the additional column (or > > 2)?? > > > Basic process for any change which modifies the structure of the table is > to create a temporary table with the new structure, copy the information > from the old table table to the new one (modifying as needed to match the > new structure), drop the old table and rename the new table to the old > tables name. So the time might be realistic. It depends on the hardware > you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: > On Thu, October 19, 2006 18:24, Ow Mun Heng said: > > Just curious to know, > > > > I tried to update a table with ~1.7 million rows (~1G in size) and the > > update took close to 15-20 minutes before it says it's done. > > Is this kind of speed expected? > > > > I don't really understand how the alter table add column is done, but > > when I look at the "show processlist" I see that it says the state is > > "copying into tmp table" > > > > Does the alter table mean that MySQL has to copy the Entire table, > > row-by-row into a temporary table, and add in the additional column (or > > 2)?? > > > Basic process for any change which modifies the structure of the table is > to create a temporary table with the new structure, copy the information > from the old table table to the new one (modifying as needed to match the > new structure), drop the old table and rename the new table to the old > tables name. So the time might be realistic. It depends on the hardware > you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Thu, October 19, 2006 18:24, Ow Mun Heng said: > Just curious to know, > > I tried to update a table with ~1.7 million rows (~1G in size) and the > update took close to 15-20 minutes before it says it's done. > Is this kind of speed expected? > > I don't really understand how the alter table add column is done, but > when I look at the "show processlist" I see that it says the state is > "copying into tmp table" > > Does the alter table mean that MySQL has to copy the Entire table, > row-by-row into a temporary table, and add in the additional column (or > 2)?? > > I'm using InnoDB by the way > Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the "show processlist" I see that it says the state is "copying into tmp table" Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update?
On Wed, 2006-10-18 at 09:29 +0800, Ow Mun Heng wrote: > Just curious to know, > > I tried to update a table with ~1.7 million rows (~1G in size) and the > update took close to 15-20 minutes before it says it's done. > Is this kind of speed expected? > > I don't really understand how the alter table add column is done, but > when I look at the "show processlist" I see that it says the state is > "copying into tmp table" > > Does the alter table mean that MySQL has to copy the Entire table, > row-by-row into a temporary table, and add in the additional column (or > 2)?? > > Btw, where is this temporary table? I don't see it in the DB. and I > don't see it in the default /tmp directory. > This answers some of the questions. http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html > I'm using InnoDB by the way. > > Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update?
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the "show processlist" I see that it says the state is "copying into tmp table" Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
