Hi, I am using your procedure on MyISAM tables now and works but RENAME does not work with locked tables,
(anyway it is already an atomic operation) =BARON================================================ Try something like this: create table new_table like old_table; alter table new_table add key(.....); insert into new_table select * from old_table where <primary_key> between ? and ?; Repeat the last statement until nearly finished, then: lock tables new_table write, old_table write; insert into new_table select * from old_table where <primary_key> > ?; rename table old_table backup, new_table old_table; unlock tables; ================================================ so the above procedure fails unless I swap like this: --------------------- ... ... unlock tables; rename table old_table backup, new_table old_table; --------------------- The problem is of course that it is no more atomic. I also thought of "SELECT for UPDATE" that locks the records but does not prevent from inserting new ones, any suggestions? Thanks Clauido
