Deleting Rows: No Single Member Has More Than x Records

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开发者_JAVA百科 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.

  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 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

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.

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