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); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
