> >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=arch...@jab.org
