Re: Re-creating tables

[mos]

At 05:55 AM 2/23/2008, Waynn Lue wrote:
>I have three or four different ALTER TABLE commands I need to run on a
>9 million row table (related to the previous email I sent).  I've
>tried running it before and it just takes way too long, so I was
>thinking the fastest way to get this done is to create new tables with
>the final schema, then drop the old tables and rename the new ones.
>
>There are a few ways to go about this.
>
>1. Stop the reads/writes to the db.  Use mysqldump, truncate the
>tables, drop the tables, recreate with the correct schema, then import
>it again.
>2. Create a new temporary table, keep the reads and writes going,
>SELECT into that new table, when it catches up, turn off the
>reads/writes for a short period of time while I truncate/drop then
>rename the temporary table.
>3. Use replication somehow to go from the old table to the new table
>(can I do that?).
>4. Create a new temporary table, stop reads/writes to it, then do an
>INSERT INTO SELECT from the old to new table.
>
>One slight problem with choice 2 is that I don't know how to make sure
>that I know when the reads/writes are done.  Not all the tables have
>an auto-increment id, so I can't just keep inserting in random ids.
>As an aside, if I do INSERT INTO SELECT, does it block any operations
>on the table that I'm SELECTing from?
>
>Thanks for any insights,
>Waynn

Waynn,
     Why are you using 3 or 4 alter table commands on the same table? Each 
command means it will create a copy of the table, makes the changes to 
that, then it renames it to the correct table name and deletes the old 
table name. You should be able to add all 4 alter table commands in 1 Alter 
Table statement, just by putting a "," between the alter specifications. 
See the syntax in the manual: 
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html This means the 
table gets rebuilt only once and not 4 times!

Mike