Re: Slow ALTER TABLE on 70M row InnoDB table

[Xn Nooby]

Below is a generic version of the code I am trying.  It does copy the
rows very quickly, but I will have to test to see how quickly the
indices are built.  Is the below code what you were suggesting?  I had
a little trouble dropping and later adding the primary index, but I
think I got it figured out.

Below I basically do this:
  make the_table_clone from the the_table
  drop the indices on the_table_clone
  copy the row from the_table to the_table_clone
  add 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;


# 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;

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;


# Add back indices in one command (for max speed)

ALTER TABLE the_table_clone \
  ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\
  ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\
  ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\
  MODIFY id INT SIGNED AUTO_INCREMENT,\
  ADD PRIMARY KEY(col1);