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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
