From: Eric Bergen Date: August 21 2010 6:56pm Subject: Re: Slow ALTER TABLE on 70M row InnoDB table List-Archive: http://lists.mysql.com/mysql/222636 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Most alter table operations in 5.0 will rebuild the entire table. The best thing to increase for alter table speed in innodb is the buffer pool. For more details on how innodb handles alter table see http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-ha= ndles-transactions/ On Wednesday, August 18, 2010, Xn Nooby wrote: > From what I have read, ALTER TABLE to add an index causes the entire > table to be duplicated, so wouldn't my ALTER TABLE command be > duplicating the work done by the SELECT command? > > > > On Wed, Aug 18, 2010 at 4:50 PM, mos wrote: >> At 02:52 PM 8/18/2010, Xn Nooby wrote: >>> >>> Below is a generic version of the code I am trying. =A0It does copy the >>> rows very quickly, but I will have to test to see how quickly the >>> indices are built. =A0Is the below code what you were suggesting? =A0I = had >>> a little trouble dropping and later adding the primary index, but I >>> think I got it figured out. >>> >>> Below I basically do this: >>> =A0make the_table_clone from the the_table >>> =A0drop the indices on the_table_clone >>> =A0copy the row from the_table to the_table_clone >>> =A0add the indices back to the_table_clone >>> >>> If this runs fast enough, I will then drop the_table, and rename >>> the_table_clone to the_table >>> >>> >>> USE the_database; >>> >>> >>> DROP TABLE IF EXISTS the_table_clone; >>> >>> CREATE TABLE the_table_clone LIKE the_table; >> >> Or you can try something like: >> >> create table the_table_clone engine=3Dinnodb select * from the_table lim= it=3D0; >> This will create the same table structure but not the indexes so you don= 't >> have to drop the indexes below. >> >> >> >>> # drop minor indices on clone >>> >>> ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; >>> >>> ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; >>> >>> ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; >>> >>> >>> # drop primary index on clone >>> >>> ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; >> >> You still need the statement above to change the autoinc to integer if y= ou >> use my Create Table... statement above. >> >> >> >>> ALTER TABLE the_table_clone DROP PRIMARY KEY; >>> >>> >>> # add 2 new columns to clone >>> >>> ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD >>> price_amount float DEFAULT '0'; >>> >>> >>> # copy rows >>> >>> INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT >>> 0,10000000; >>> >>> #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; >> >> Why do you have two insert statements? If you are inserting a group of >> records at a time then you need a limit statement on each, and increment= the >> offset by the number of rows that have been added. >> >> I would explicitly specify the column list for both the Insert and the >> Select to make sure they match up. There is no point going through all t= his >> if it inserts the data into the wrong columns! >> Check the data before creating the indexes to make sure the same number = of >> rows have been copied over and the data is in the correct columns. >> >> >> >>> # Add back indices in one command (for max speed) >>> >>> ALTER TABLE the_table_clone \ >>> =A0ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ >>> =A0ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ >>> =A0ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ >>> =A0MODIFY id INT SIGNED AUTO_INCREMENT,\ >>> =A0ADD PRIMARY KEY(col1); >> >> Correct. >> >> Mike >> >> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Deric.bergen@g= mail.com > > --=20 Eric Bergen eric.bergen@stripped http://www.ebergen.net