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_c

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

DELETEFROM member_logins
WHERE member_id=?
ORDERBY id DESC
LIMIT 50,10;

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

Solution 2:

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

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

While (@MemberCount>=50)
BeginSet@Id=SelectMin(id) from member_logins where member_id =@memberidDeletefrom member_logins where id =@IdSet@MemberCount=@MemberCount-1End

Now insert the new member login record.

Solution 3:

DELETE  m
FROM member_logins AS m
  JOIN ( SELECTDISTINCT 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
          ORDERBY mx.date_created DESC
          LIMIT 1 OFFSET 49
        ) 

The subquery

JOIN ( SELECTDISTINCT 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