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