I am feeling more motivated to interrupt this operation now. What would be an effective way to do that? As a reminder, I submitted this operation using the GUI administration tool --- specifically the table editor. The whole tool is unresponsive while this operation is running. I can run and use another instance of the GUI administration tool.
If I succeed in interrupting this operation, presumably the rollback will take quite a while. How can I tell whether I have successfully interrupted the operation and the rollback is what's nailing my machine? Thanks, Mike Spreitzer SMTP: [EMAIL PROTECTED], Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Mike Spreitzer/Watson/[EMAIL PROTECTED] 02/19/08 08:34 PM To "Baron Schwartz" <[EMAIL PROTECTED]> cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Thanks, Baron. Yes, the table is bigger than memory. It took about 2.5 days to create the table, inserting about 7,000 rows at a time; this column and index addition has been running for about a day now. I notice you did not say it was terribly stupid to create this index before putting the final data in the new column. So I infer there is no big motivation to interrupt the operation I have going. Thanks, Mike "Baron Schwartz" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/19/08 08:13 PM To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Hi, On Feb 19, 2008 5:20 PM, Mike Spreitzer <[EMAIL PROTECTED]> wrote: > I am new to MySQL, and wonder if I have done something terribly stupid. I > have an InnoDB table with 27 million rows. Without thinking very much, I > issued the following command through the GUI administration tool: > > ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL AFTER > `el_index`, > ADD INDEX `el_p2`(`el_p2`(150)) > > Of course, all the values in this column will be the same. The index > will, if this ever completes, indicate that one value is associated with > all 27E6 rows. My primary question is, will this take O(27E6) time or > something worse (e.g., O(27E6 squared) time)? > > Of course my plan is to eventually put some interesting data in that new > column. I do not really need the index until the data is there. > > This database is not being used on-line, this is just for study, so I do > not mind large batch operations. I just don't want to be grossly stupid > in my choice of batch operations. > > My second question is: if I have indeed done something grossly stupid, > what is the best (if there is any at all!) way to interrupt it and > proceed. If the table is bigger than memory, building the index will be very slow. If you interrupt it, it's just going to roll back everything it's done so far. So you have the choice of either letting it finish and then dropping the index, or killing it and letting it roll back. (You can't prevent the roll back, even if you restart).
