MySQL Forums
Forum List  »  General

Re: Performing table inserts/selects while a alter-table query is running?
Posted by: Rick James
Date: May 11, 2010 07:47AM

ALTER TABLE, on MyISAM or InnoDB, will block all access during the operation. It will take a time proportional (or worse) than the size of the table. You can see the size via SHOW TABLE STATUS -- look for data_length and index_length; number of rows is less relevant.

ALTER runs at a gigabyte per hour (more or less, depending on disk subsystem, indexes, etc).

If you decide you have to have a non-ALTER way of achieving the goal, we can talk further. But I would need to see the SHOW CREATE TABLE, SHOW TABLE SIZE, and have some feel for the semantics of any PRIMARY and UNIQUE keys. And whether there are any FOREIGN KEYs in this table or referring to this table. I am envisioning a complex scheme to copy the table over, while live, and play 'catchu', and finally do RENAME TABLE. Also vital to the scheme is knowing how INSERTs/UPDATEs happen. Down time can be minimal.

A simpler technique is to build a parallel table with the added column (or the widen column, or added index). This can be done with no downtime. But it requires code changes. And it may not be practical. Again, please show the above info so we can discuss its applicability.

BTW,
CREATE TABLE new (augmented fields/indexes);
INSERT INTO new SELECT ... FROM real;
RENAME TABLE real TO old, new TO real; -- do both in same statement.
is another technique. But it is likely to take about the same amount of time as the ALTER. I am unclear on how much it would help to turn off indexes during the INSERT. It would not have as much downtime for _reads_, but it does need to block _writes_.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.