Yes, that will lock up the table while the change is being made. One
technique you can use is to rename the table and create a new to catch
the incoming data.
RENAME TABLE x TO y;CREATE TABLE x LIKE y;
By putting both commands on 1 line, it will execute almost immediately.
Then you can alter the table you renamed to y, since it's not being used
it won't lock up anything. Then rename it back when you are done. If you
have an auto increment field in the table, make sure you set the auto
increment value to a number higher than what accumulated in the
temporary holding table.
ALTER TABLE y AUTO_INCREMENT = ###########
RENAME TABLE y TO tmp, x TO y, tmp TO x
That will swap the tables back, y becomes x, and x become y. The you can
do an insert select from table y, which is holding the data that
accumulate while you were altering the other table.
It's convoluted and round about, but you won't lose any data. And it may
not be feasible if there are data dependencies within the same table.
Baron Schwartz wrote:
Hi Brian,
Brian Dunning wrote:
...if I add a column to a table with 40,000,000 records, will it
cause that table to hang for any significant amount of time, and
prevent other transactions? It's a MyISAM table and I was going to
add a varchar(20) column, NULL. It's a very busy table, constant
searches and inserts.
Yes, definitely. If you look up the ALTER TABLE documentation it will
explain the details -- I think all SELECTs will continue to run okay,
but INSERTs will wait until the ALTER is done, then they will be
applied to the altered table.
But don't hold me to that :-)
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
