MySQL: Delete all but last N records

Question

Consider the following table:

mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | bigint(20)  | NO   | PRI | NULL              | auto_increment |
| pixel_id      | varchar(32) | NO   | MUL | NULL              |                |
| creation_time | timestamp   | NO   | MUL | CURRENT_TIMESTAMP |                |
| pixel         | mediumblob  | NO   |     | NULL              |                |
+---------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.04 sec)

With some data:

mysql> SELECT * FROM pixels;
+----+----------------------------------+---------------------+----------------------------------+
| id | pixel_id                         | creation_time       | pixel                            |
+----+----------------------------------+---------------------+----------------------------------+
|  1 | 0d1b042671e0f8c1d1f226abe923583c | 2012-07-01 14:42:26 | 2d8292a62e89fcbf8b1592cf53f0dc86 |
|  2 | 9192b7491ac9321ed67c198834965580 | 2012-07-01 14:42:26 | f41a4a3e1a5f2f25c02f2e377627355c |
|  3 | 82b6ad645a4c75a552c0ddfd8d07c38a | 2012-07-01 14:42:27 | bffd2be16fcb82d0592aaa00fe0ebb9d |
|  4 | de41f4932ee7e90bed2e26d4e7e1937a | 2012-07-01 14:42:27 | 6632df3642ce3465ee5160126f20d837 |
|  5 | f98ac2c09574e2accb6cff709ac8a97f | 2012-07-01 14:42:27 | 00d1a3d9e9b51d7e5f66120203189107 |
|  6 | e90a3233fd9054fb3c23d04b03a8dde8 | 2012-07-01 14:42:27 | 4d20a996a46b9767d8c3f6708cb0ce88 |
|  7 | 08177f9f44f3d6fa515bd1a1983a7b45 | 2012-07-01 14:42:28 | ed3a572da6d05d34f5928035bc67d5be |
|  8 | 9d9138ffb7df537d61276a91e837a327 | 2012-07-01 14:42:28 | 3be6876351254ffa4a00364cd3e8c10e |
+----+----------------------------------+---------------------+----------------------------------+

I'm trying to delete all the records except the last, say, 5 rows. "Last" is determined by id, as creation_time may be duplicate.

I've tried:

mysql> SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5;

And got the reasonable answer:

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

I've tried to use it as a subquery:

mysql> DELETE FROM pixels WHERE id < 
      (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5);
ERROR 1093 (HY000): You can't specify target table 'pixels' for update in FROM clause

Tried another approach:

mysql> DELETE FROM pixels WHERE id NOT IN 
      (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 5);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So, How do I delete all records except for the N most recent?

Answer 1

You need to change your subquery approach slightly - moving the condition from the WHERE clause to a join, to bypass MySQL limitations.

If the id and creation_time always define the same ordering, you can use this:

DELETE p
FROM 
       pixels AS p
   JOIN
       ( SELECT id 
         FROM pixels 
         ORDER BY id       
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.id < lim.id ;

Since that may not be true, and the two orderings may sometimes differ, resulting in deleting more or fewer rows than 5, you could use this, which will be more accurate but probably slower with a big table:

DELETE p
FROM 
       pixels AS p
   JOIN
       ( SELECT creation_time, id 
         FROM pixels 
         ORDER BY creation_time DESC, id DESC
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.creation_time < lim.creation_time
     OR p.creation_time = lim.creation_time AND p.id < lim.id ;

Answer 2

Try this exclusion join which will delete any ids that are not matched by the subquery.

DELETE p FROM pixels p
LEFT JOIN 
  (SELECT id 
   FROM pixels
   ORDER BY creation_time DESC
   LIMIT 5) p2 USING(id) 
WHERE p2.id IS NULL;

Avoid NOT IN (SELECT...) in MySQL at (nearly) all costs. It executes the subquery for each row in the outer query. This has been fixed in the unreleased MySQL 5.6.

Answer 3

Keeping the simplicity of your original post I would suggest the following approach, which is also much easier to follow. (assuming ; is the statement delimiter on your system)

SELECT @first_id_to_keep := id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5;
DELETE FROM pixels WHERE id < @first_id_to_keep;