> CREATE TABLE new_table (<all like your current one, besides indexes>) > TYPE=BDB; > INSERT INTO new_table SELECT * FROM old_table; > CREATE INDEX ... ON new_table;... > ALTER TABLE new_table ADD PRIMARY KEY (...);... > RENAME old_table TO old_table_bak; > RENAME new_table TO old_table; > > That should cause a downtime of less than a second...
Interesting suggestion... I have about a million rows in that table, but I'll try it... > last but not least you need insert all rows that have been created > between the INSERT and the last RENAME. Well, if its less than a second I can afford to have the database down for that long - its the "several days" that is problematical. *quick test* About 3 minutes to do thecopy -not bad at all! I've been doing some experimentswith BDB tables though and am having real problems- of the "locking up the mysql server" type! I have a small benchmark I use to check the speed of my main table, which locks the tbale, does two updates n a single rowof that table and unlocks it again. I run many of these in parallel (up to 100) to get a ffeel for the load. Running these tests on the BDB version of the table I have found that after aout 30 connections I get Can't lock file (errno: 12) at which point the server locks up. It will not shutdown properly, and just hangs - eventually needing to be killed by hand. Maybe BDB tables are not for me after all... -pcf. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
