Hi Mike, my comments are below: On Wed, Aug 18, 2010 at 4:50 PM, mos <mo...@fastmail.fm> wrote: > At 02:52 PM 8/18/2010, Xn Nooby wrote: >> >> 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; > > Or you can try something like: > > create table the_table_clone engine=innodb select * from the_table limit=0; > This will create the same table structure but not the indexes so you don't > have to drop the indexes below.
That is good to know. I did not mind dropping the indices in this case, because the table was still empty. > > > >> # 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 you > 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. The 2nd INSERT is disabled with the # character. I am using the statement with the LIMIT for testing, and will switch to the other command when I want to process all the records. > > 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 this > 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. Okay. I thought it was safe to assume that the new columns would appear on the "right-side" of the column list. > > > >> # 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); > > Correct. The insert took 7 minutes on 10M rows, and that ALTER command took another 46 minutes. > > Mike > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
