How to delete records matching the 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 would do is to remove the recording n , where n - the total number of records that match the condition of less than 20. The table is large enough (more than 1 meter records).

The condition is complex, which includes a relative date query for frequently changing data and includes joining a couple of other tables, so first select how many of them match the condition and then delete twenty less, I will work as far as I know.

The condition is as follows:

• prize_details_id - x (you need to run the same request for several different identifiers)
• available_at is null
• prize_id does not exist in another table
• status 1
• tier (in prize_detail table) above y

Answer 1

Maybe so:

• create a temporary table _tmp (id int auto_increment, prim_id int)
• select the appropriate arrival identifiers in this temporary table.
• remove from win where id (select priority_id from _tmp, where id> = 20)

Answer 2

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) 

Answer 3

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

Where is the order defining the "last" 20 lines