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
There will be no "gap" in-between.
From: Jerry Schwartz [mailto:jschwartz@stripped]
Sent: Friday, August 21, 2009 8:32 AM
To: 'mos'; 'MySQL'
Subject: RE: Scaling Mysql
> 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
>3) insert into alt_send_sms select * from send_sms_full; drop table
>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
[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.
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
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.