From: Shawn Green
Date: January 21 2010 2:02pm
Subject: Re: Selecting, Inserting and Deleting data
List-Archive: http://lists.mysql.com/mysql/220335
Message-Id: <4B585E6F.3050603@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; CHARSET=US-ASCII; format=flowed
Content-Transfer-Encoding: 7BIT

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