Re: Re-creating tables

[Waynn Lue]

That's actually why I'm dropping/recreating, because I thought the
changes I have to make require multiple statements.  Let me know if
that's a wrong assumption, here's what I have to do.

1.  drop two foreign keys from Users to Actions (in the previous
example I gave).
2.  expand INT to BIGINT on Users
3.  expand INT to BIGINT on Actions
4.  recreate two foreign keys from Users to Actions.

That's four alter statements, which each require making temporary
table copies, so I assumed dropping/recreating was faster.

On Sat, Feb 23, 2008 at 2:42 PM, mos <mos99@stripped> wrote:
>
> 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/mysql?unsub=1
>
>