RENAME statement is atomic, and you can specify multiple tables to rename at once.
Instead of two statements, do this: rename table send_sms to send_sms_full, send_sms_empty to send_sms; There will be no "gap" in-between. -----Original Message----- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: Friday, August 21, 2009 8:32 AM To: 'mos'; 'MySQL' Subject: RE: Scaling Mysql > >Krishna, > Rather than copying rows from one table to another, and deleting the >previous rows, why not just do: > >1) create table send_sms_empty like send_sms; > >2) rename table send_sms to send_sms_full;rename send_sms_empty to send_sms; > >3) insert into alt_send_sms select * from send_sms_full; drop table >send_sms_full; > >because step #2 is two sql statements, they will get executed together and >will take just 1 or 2 ms and now you have an empty table that continues to >get filled. This eliminates the insert delete table locking. Plus you >always start with an empty optimized table. > >Step #3 uses a drop table which is much faster than trying to delete the rows. > [JS] You'd have to make sure that the application, which is after all pounding the database pretty hard, doesn't gag. As fast as that operation might be, the application is likely to collide with it. You cannot rename a locked table, so I'm not sure how you could do what you are suggesting in an ACID way. You'd need some kind of semaphore somewhere. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com >Mike > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
