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]
