Hi Krishna, As table is using MyISAM engine and it acquires a table level lock, the queries will be executed one after one . By converting it into Innodb as it acquires a row level lock, doing a select and delete based on primary key will be faster and the concurrency increases.
-- Thanks Suresh Kuna MySQL DBA On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi List, > > I am working for a messaging company, sending sms to enterprise customers. > > In a mysql table data is being continuously inserted by user. Most of the > time we have 5 to 10 millions of data in this table. > > Table name : alt_send_sms engine myisam > > From this table, i need to select data based on below parameter. Send some > where else and then delete the selected data. > > selection and deletion part is done in bulk. > > SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, > service, account, id, sms_type, mclass, mwi, coding, compress FROM > alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20 > > delete from alt_send_sms where sql_id in > (....................................................) > > sql_id is a unique bigint column with auto_increment. > > Since the selection and deletion is done in bulk. Therefore, i cannot run > many similar concurrent queries. As duplicate messages will be send. What > can be the solution for this ? > > Any response is highly appreciated. > > Thanks, > Krishna >
