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