Deleting Rows: No Single Member Has More Than x Records

Question

I have a table structured like this:

CREAT TABLE `member_logins` (
    `id` bigint(10) unsigned not null auto_increment,
    `member_id` mediumint(8) unsigned not null,
    `date_created` datetime not null,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;

I only want to keep 50 logins recorded for each member. So I'm looking for a way to DELETE rows on a per member basis, any rows past the most recent 50.

Edit: I should have mentioned... This would be a nightly cron job. Not something that needs to be done in real time.

score 2 · Accepted Answer · 2011-07-26T11:41:00.310

  DELETE FROM member_logins
  WHERE id in(
       SELECT ID
       FROM (SELECT 
               ID,member_id, 
               IF( @prev <> member_id, @rownum := 1, @rownum := @rownum+1 ) AS  rank, 
               @prev := member_id,date_created 
               FROM member_logins t 
               JOIN (SELECT @rownum := NULL, @prev := 0) AS r 
            ORDER BY t.member_id,t.date_created desc) as tmp
        where tmp.rank > 2)

Fixed the query. Tested on sample data and it works for me.

Sample Data i loaded for testing

id  member_id   date_created
1   1   2/26/2011 12:00:00 AM
2   1   5/26/2011 12:00:00 AM
3   1   4/26/2011 12:00:00 AM
4   2   5/26/2011 12:00:00 AM
5   2   3/26/2011 12:00:00 AM
6   2   4/26/2011 12:00:00 AM

Instead of 50, i have in the query top 2 rows for testing.

So my query should delete all rows which are having rank > 2 in each member_id group where in rows ordered by date_created desc.

Output after running the delete query:

id  member_id   date_created
2   1   5/26/2011 12:00:00 AM
3   1   4/26/2011 12:00:00 AM
4   2   5/26/2011 12:00:00 AM
6   2   4/26/2011 12:00:00 AM

You can see the rows with ID 1 and 5 got deleted. These are the rows having rank > 2 in each member_id group

This isn't working for me. I tested on a table where SELECT COUNT(*) FROM member_logins WHERE member_id = 1, returns 80+ rows, but running your query shows 0 rows affected. I even changed the DELETE to a SELECT, and it returns 0 rows. — mellowsoon, Jul 26 '11 at 03:45

symcbean · Answer 2 · 2011-07-26T16:37:59.567

1

This would be a nightly cron job

No - not the right way to fix the problem - batch jobs are difficult to manage and test, and for most internet facing sites there is no daily downtime. It's much better to spread the load, and only run the code when you need to, i.e. when a user logs in....

DELETE FROM member_logins
WHERE member_id=?
ORDER BY id DESC
LIMIT 50,10;

Which will be a lot more efficient with an index on member_id

edited Jul 26 '11 at 16:37

answered Jul 26 '11 at 12:42

symcbean

47,736
6
59
94

He probably needs `ORDER BY date_created DESC` but this is best I think. Simple and efficient. – ypercubeᵀᴹ Jul 26 '11 at 12:46
@ypercube - yes - I've got the sorting the wrong way around - well spotted. It should be DESC to preserve the most recent 50 (about to amend answer) but in the absence of staggered insert ids (i.e. for master-master replication) the ids will increase along with the date_created. – symcbean Jul 26 '11 at 16:37

score 0 · Answer 3 · answered Jul 26 '11 at 03:08

PsuedoCode - Most likely this would go in stored proc or program logic:

Set @MemberCount = Select Count(member_ID) from member_logins where member_id = @memberid

While (@MemberCount >= 50)
Begin
 Set @Id = Select Min(id) from member_logins where member_id = @memberid
 Delete from member_logins where id = @Id
 Set @MemberCount = @MemberCount - 1
End

Now insert the new member login record.

ypercubeᵀᴹ · Answer 4 · 2011-07-26T12:39:12.883

DELETE  m
FROM member_logins AS m
  JOIN ( SELECT DISTINCT member_id
         FROM member_logins
       ) AS md
    ON  md.member_id = m.member_id
    AND m.date_created <
        ( SELECT mx.date_created
          FROM ( SELECT *
                 FROM member_logins
               ) AS mx
          WHERE mx.member_id = md.member_id
          ORDER BY mx.date_created DESC
          LIMIT 1 OFFSET 49
        )

The subquery

  JOIN ( SELECT DISTINCT member_id
         FROM member_logins
       ) AS md
    ON  md.member_id = m.member_id

can be replaced by a simple SELECT from your member table:

  JOIN member AS md
    ON  md.member_id = m.member_id

Deleting Rows: No Single Member Has More Than x Records

4 Answers4

Linked