At 01:30 AM 8/21/2009, Krishna Chandra Prajapati wrote:
Hi list,
I have two tables send_sms and alt_send_sms. Users are inserting records
into send_sms @ 500/sec ie 30000/min. After applying some updates to
send_sms data are transferred to alt_send_sms and deleted from send sms. The
same thing is happening with alt_send_sms table.
Is it possible to insert 1000records/sec in send_sms table and taken out at
the rate 1000records/seconds from alt_send_sms.
Which engine is more better for the above senario.
Regards,
Krishna
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.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org