Re: Re-creating tables

[Waynn Lue]

The problem here though is that there is no MyTable.  There are two
separate tables, Users and Actions, and I can't alter both of them in
the same statement, as far as I know.  As a result, when I alter just
Users, that fails because there's an FK between Users and Actions and
the type of the two columns is now different.

On Mon, Feb 25, 2008 at 7:50 AM, mos <mos99@stripped> wrote:
> At 05:16 PM 2/24/2008, Waynn Lue wrote:
>  >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.
>
>  Each of your Alter statements will mean a temp table is created, the data
>  is moved over, the changes are made, and the indexes are rebuilt.
>  It will be 4x faster if you do it all in one Alter statement. Since the
>  alter statement will rebuild the keys at the end, is there really a need to
>  to drop the foreign keys or is this an InnoDb quirk?
>
>  Try something like:
>
>  alter table MyTable change column Users Users BigInt, change column Actions
>  Actions BigInt;
>
>  You normally would drop indexes to speed things up when loading a lot of
>  data into the table, then rebuild the indexes after the data has been
>  loaded. But since Alter table does this anyways, you're not accomplishing
>  anything by doing it manually.
>
>  Mike
>
>
>
>
>  >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
>  > >
>  > >
>
>  --
>
>
> MySQL General Mailing List
>  For list archives: http://lists.mysql.com/mysql
>  To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>