🔒 Locked

[web] MySQL: Delete all but the latest N entries of a table

Started by Basiror Feb 22, 2006 at 5:20 AM 3 replies 1.5k views

Original Post

Feb 22, 2006 at 5:20 AM

Hi I am working on MySQL 5 and want to delete all entries but the lastest 50 entries from a table. Unfortunately MySQL doesn t support DELETE FROM LIMIT 49,999999; So one way I thought of would be to SELECT the 50th auto_increment column entry into a variable and delete all entries with auto_column < variable My question, is there a one-liner that could do the same without searching the table before deleting? This command is executed quite often so I need to implement a somewhat efficient solution thx in advance

ApochPiQ

Feb 22, 2006 at 5:38 AM

In MySQL, the easiest solution I know of is pretty much as you said:

select key from table order by key desc limit 49, 1;delete from table where key > [50th];

If you're indexed properly on the key field this won't be a performance issue at all; it's basically the same thing as the DB engine would do anyways.

Basiror

Feb 22, 2006 at 5:50 AM

yeah i use InnoDB and there indices are BTREEs by default so you have logarithmic search complexity

thx

markr

Feb 22, 2006 at 7:09 AM

In MySQL 4.1 + you can do it with a subquery:

delete from table where key >    (select key from table order by key desc limit 49, 1);

Mark

Basiror

Feb 22, 2006 at 7:16 AM

yeah this way one can get rid of the variables although this shouldn t have an impact speedwise

thx

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.