MySQL SELECT and then DELETE

Question

I am in the process of setting up a a multi-server installation where some user data gets synchronized across servers depending on the user's priority rating (a number going from 0 to ...). The synchronization is intended to be done as a lazy background job with users with a higher priority seeing their data synchronized faster.

To that end I have a DBTable

CREATE TABLE IF NOT EXISTS `spiegel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`priority` tinyint(1) NOT NULL,
`cql` varchar(4096) NOT NULL,
PRIMARY KEY (`id`),
KEY `priority` (`priority`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

where all non-zero priority users have their INSERT/DELETE/UPDATE transactions noted. A cron job periodically (every 10 minutes) runs a PHP script that takes a few (I want to limit this to N entries, say, 100) entries and deals with the process of synchronizing the databases on the other servers.

What I want to do is

Pickup a maximum N entries ordered by their priority
Removing those entries from the table

The first bit is ok

SELECT * FROM `spiegel` ORDER BY priority LIMIT 100;

This is where my knowledge of SQL is letting me down. It is not clear to me how I can efficiently remove the "picked up" entries. The best I have been able to do is to create a CSV list of the ids of the removed entries and then use a

DELETE FROM `spiegel` WHERE id in idcsvlist

However, I suspect that this is a long winded way of doing things. I'd be much obliged to anyone who might be able to suggest a better approach.

You could just store the items to delete in a temporary table, use the delete statement with the where clause selecting the ids from that temp table, and create an on before delete trigger to your spiegel table to log what has been processed. — rekaszeru, Apr 26 '14 at 06:01
Why can't you use MySQL's master-master or master-slave synchronization, rather than doing it yourself? — Amil Waduwawara, Apr 26 '14 at 06:05
Two good reasons a. I do not want wholesale synchronization. It is only meant to happen for selected users provided their priority setting is adequate. b. I looked at MariaDBs Galera technology. Synchronization across data centers introduces latency that would have an impact on my application - a price not worth paying given that the synchronized data will be used somewhat infrequently. — DroidOS, Apr 26 '14 at 06:52

score 2 · Accepted Answer · edited May 23 '17 at 10:32

2

mySQL subquery limit prblm Juz Refer the link

Try thiz :

SELECT * FROM `spiegel` ORDER BY priority LIMIT 100;

Then

DELETE FROM spiegel WHERE id IN (select * from(SELECT id FROM `spiegel` ORDER BY priority LIMIT 100)as temp);

edited May 23 '17 at 10:32

Community

1
1

answered Apr 26 '14 at 06:17

Hrithu

281
4
13

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

This is the typical way to do it (taken from https://stackoverflow.com/a/4041332/3565972)

DELETE FROM `spiegel` WHERE id IN (SELECT id FROM `spiegel` ORDER BY priority LIMIT 100);

edited May 23 '17 at 12:03

Community

1
1

answered Apr 26 '14 at 06:03

savanto

4,470
23
40

score 0 · Answer 3 · answered Jun 21 '14 at 03:23

0

A note for anyone running into this thread. In order to deliver the results I was after you need

ORDER BY priority DESC

The DESC bit was missing in the answers here.

answered Jun 21 '14 at 03:23

DroidOS

8,530
16
99
171

MySQL SELECT and then DELETE

3 Answers3