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