mysql / sql: how to delete all rows except the Nth last per user?

Question

I have a message (id, userid, message) table that grows rapidly.

I would like to delete all messages per user except his last 30

ex: if user1 has 100 messages, we will delete the first 70,

if user2 has 40 messages, we will delete the first 10,

if userN has 10 messages, no action is taken

Is there a way to do it with a single SQL ?

My idea for now is to make a LOOP with PHP and lake N sql, which is very long for N users.

`LIMIT` can be used here, see [Mysql delete order by](https://stackoverflow.com/questions/12382250/mysql-delete-order-by) (You will need an ordinal column of some kind as natural order is meaningless) — Alex K., Jun 14 '18 at 12:53
You might want to investigate other ways to achieve the performance that you want. Large deletes are expensive and a history of messages is useful. — Gordon Linoff, Jun 14 '18 at 13:10
Some users have 200,000 messages. We only display LAST N messages. Do you have a better idea ? — yarek, Jun 14 '18 at 14:02

kiran gadhe · Answer 1 · 2018-06-14T16:21:57.150

0

SET @row_number = 0;

DELETE FROM MESSAGE
     WHERE ID IN 
     (   SELECT ID FROM 
        (SELECT  ID,
          @row_number:=CASE
        WHEN @userid = userid THEN  
                       @row_number + 1
        ELSE 1
    END AS num,
    @userid:=userid as userid
    FROM MESSAGE) A
    WHERE NUM > 70 )

edited Jun 14 '18 at 16:21

answered Jun 14 '18 at 13:24

kiran gadhe

733
3
11

score 0 · Answer 2 · answered Jun 14 '18 at 13:54

MySQL (pre 8.0) doesn't have a really convenient way to do this. One method uses variables to enumerate the values:

select m.*,
       (@rn := if(@u = userid, @rn + 1,
                  if(@u := userid, 1, 1)
                 )
       ) as seqnum
from (select m.*
      from messages m
      order by userid, id desc
     ) m cross join
     (select @u := -1, @rn := 0) params;

You can turn this into a delete using join:

delete m
    from messages m join
        (select m.*,
                (@rn := if(@u = userid, @rn + 1,
                           if(@u := userid, 1, 1)
                          )
                ) as seqnum
         from (select m.*
               from messages m
               order by userid, id desc
              ) m cross join
              (select @u := -1, @rn := 0) params
        ) mm
        on m.id = mm.id
where seqnum > 30;

As I say in a comment, I don't think this is a good solution for a real-world problem. The history of messages is useful and there are probably other ways to achieve the performance you want. The difference between 30 messages for a user and 70 messages for a user should not have that much of an effect on performance, in a tuned system.

I mean : some users have 50,000 messages an dI want to keep only the last ones. DO you have any suggestion / ideas ? — yarek, Jun 14 '18 at 14:01

mysql / sql: how to delete all rows except the Nth last per user?

2 Answers2