Keep only 10 records per user

Question

I run points system on my site so I need to keep logs of different action of my users into database. The problem is that I have too many users and keeping all the records permanently may cause server overload... I there a way to keep only 10 records per user and automatically delete older entries? Does mysql have some function for this?

Thanks in advance

Answer 1

Just run an hourly cron job that deletes the 11th - n records.

Answer 2

Before insert a record you could check how many the user has first. If they have >=10 delete the oldest one. Then insert the new one.

Answer 3

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table WHERE USER_ID = 1) AND USER_ID = 1

Clearer Version

DELETE FROM Table
    WHERE ID NOT IN
    (
        SELECT TOP 10 ID  FROM Table WHERE USER_ID = 1
    )
    AND USER_ID = 1

Answer 4

When you are inserting a new record for a user. Just do a query like this before (Don't forget the where-condition):

DELETE FROM tablename WHERE userID = 'currentUserId' LIMIT 9, 999999

After that you can insert new data. This keeps the data always to ten records for each user.

INSERT INTO tablename VALUES(....)

Answer 5

If your goal is to have the database ensure that for a given table there are never more than N rows per a given subkey (user) then the correct way to solve this will be either:

• Use stored procedures to manage inserts in the table.
• Use a trigger to delete older rows after an insert.

If you're already using stored procedures for data access, then modifying the insert procedure would make the most sense, otherwise a trigger will be the easiest solution.

Alternately if your goal is to periodically remove old data, then using a cron job to start a stored procedure to prune old data would make the most sense.

Answer 6

You can add a trigger that takes care of removing old entries. For instance,

DELIMITER //
CREATE definer='root'@'localhost' TRIGGER afterMytableInsert AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN
    DELETE FROM MyTable WHERE user_id = NEW.user_id AND id NOT IN 
    (SELECT id FROM MyTable WHERE user_id = NEW.user_id ORDER BY action_time DESC LIMIT 10);
END//