How can I delete records that match a condition, leaving 50?

Question

Consider the following table:

CREATE TABLE `prize` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `prize_details_id` bigint(20) NOT NULL,
  `status` tinyint(4) DEFAULT '0',
  `available_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `prize_details_id_idx` (`prize_details_id`),
  KEY `status_idx` (`status`),
  KEY `available_at_idx` (`available_at`),
  CONSTRAINT `prize_prize_details_id_prize_detail_id` FOREIGN KEY (`prize_details_id`) REFERENCES `prize_detail` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1611419 DEFAULT CHARSET=latin1;

What I'd like to do is delete n records, where n is the total number of records that match a condition less 20. The table is reasonably big (greater than 1m records).

The condition is a complex one that involves a relative date query on frequently changing data, and involves a join on a couple of other tables too, so first selecting how many there are that match the condition and then deleting twenty fewer isn't going to work as far as I know.

The condition is all of the following:

prize_details_id is x (need to run the same query for a few different IDs)
available_at is null
prize_id doesn't exist in another table
status is 1
tier (on the prize_detail table) is above y

So, ultimately, are the last records to keep the 20 latest (that match all the other criteria) according to some date? The highest? What makes these last few special? — MPelletier, Nov 22 '11 at 18:33

score 2 · Accepted Answer · answered Nov 22 '11 at 18:36

2

Maybe like this:

create a temporary table _tmp (id int auto_increment, prize_id int)
select matching prize ids into this temporary table
delete from prize where id in (select prize_id from _tmp where id >=20)

answered Nov 22 '11 at 18:36

SVD

4,743
2
26
38

Good idea, I always forget about temp tables. Will try and update – BenLanc Nov 22 '11 at 18:37
This and @newtover's answer below did the job for me. It was VERY slow (~15 minutes), but did the job eventually. – BenLanc Nov 23 '11 at 12:05

score 1 · Answer 2 · answered Nov 23 '11 at 07:59

A possible hint:

mysql>
mysql> CREATE TABLE test (
    ->   id SMALLINT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test () VALUES
    ->   (),(),(),(),(),(),(),(),(),(),
    ->   (),(),(),(),(),(),(),(),(),(),
    ->   (),(),(),(),(),(),(),(),(),();
Query OK, 30 rows affected (0.01 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> DELETE FROM t USING
    -> test t JOIN (
    ->     SELECT id
    ->     FROM (
    ->         SELECT id
    ->         FROM test
    ->         ORDER BY id DESC
    ->         LIMIT 20, 1000
    ->     ) as ids) as ids ON t.id = ids.id;
Query OK, 10 rows affected (0.01 sec)

mysql> SELECT * FROM test;
+----+
| id |
+----+
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
+----+
20 rows in set (0.00 sec)

Magnus · Answer 3 · 2011-11-22T18:50:18.123

0

DELETE FROM prize
WHERE 
   id IN(--Some query--) AND
   id NOT IN(SELECT id FROM --Some query-- ORDER BY --some column-- LIMIT 20 )

Where the order by identifies the "last" 20 rows

edited Nov 22 '11 at 18:50

answered Nov 22 '11 at 18:36

Magnus

45,362
8
80
118

1

I think he wants to delete all but the last 20...doesn't your query delete the first 20? – Johnie Karr Nov 22 '11 at 18:37
@MrJamin ok, didn't know it was that awkward – Magnus Nov 22 '11 at 18:47

How can I delete records that match a condition, leaving 50?

3 Answers3