Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is id guaranteed to be contiguous? If so, use max() –  Phil Jul 1 '12 at 12:37
    
Tried that too, without success. Could you please post a query? –  Adam Matan Jul 1 '12 at 12:43
add comment

2 Answers

up vote 5 down vote accepted

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 define always 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 differ sometimes, resulting in deleting more or less rows than 5, you could 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 ;
share|improve this answer
    
I like the more accurate part. ^_^ –  zinking Jul 2 '12 at 6:55
add comment

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.

share|improve this answer
    
I think you are cofusing IN with NOT IN performance. See this blog post by Quassnoi: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL –  ypercube Jul 1 '12 at 23:04
    
Yeah, I've read that post before, but the conclusions reached by the NOT IN example differs from what I have seen. I will revisit. Either way, I think that writing the query as a join is much clearer and will perform well. –  Aaron Brown Jul 2 '12 at 0:26
    
Incidentally, not in and left join are not equivalent if there are nulls in the join key (not an issue in this case, but can catch you if one is not aware of it). Also note the performance differences between the left join and not in. blog.9minutesnooze.com/sql-not-in-subquery-null –  Aaron Brown Jul 2 '12 at 0:33
    
Hm, differences in performance may have to do with the cardinalities of the two tables (external/internal). –  ypercube Jul 2 '12 at 0:33
    
The performance difference stems from the way mysql evaluates the subquery...it is reexecuted for each row in the outer table. I will try to find a reference when I'm not on a phone. –  Aaron Brown Jul 2 '12 at 0:38
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.