Krishna Chandra Prajapati 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
The main thing you want to achieve is to isolate these rows from other
queries, correct? One easy way around this is to "tag" each row with a
unique value.
UPDATE <your table> SET tag_column=<some unique value> WHERE <your
conditions> AND tag_column=0
The last part, "AND tag_column=0" is the part that ensures that only
untagged columns that meet your condition are tagged with your unique
identifier. You can repeat this tagging process on several tables (using
the same value) to build a set of related values for your processing needs.
After you have set your values, you have identified an entire set of
data that you want to manipulate. When you are done processing the
SELECT, you can very easily drop just those rows by
DELETE FROM <your table> WHERE tag_column=<your unique value>
Or you can return those rows into the general pool of data by simply
clearing the flag
UPDATE <your table> SET tag_column = 0 WHERE tag_column = <your unique
value>;
Some ideas for unique values:
* the thread number from within your application.
* the connection number for your MySQL client
* a UUID value
* a hashed value of a combination of pseudo-random values (IP address,
time, someone's name, a random number, etc.) .
* some sequential number you track in another table.
All you really need is a number statistically improbable to repeat
between any two of your clients.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
