Keep only 10 entries per user

Question

I run the points system on my website, so I need to keep logs of the various actions of my users in the database. The problem is that I have too many users and constantly saving all the records can lead to server overload ... Do I have a way to save only 10 records per user and automatically delete old records? Does this function have mysql?

Thanks in advance

Answer 1

You can add a trigger that deletes old records. 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//

Answer 2

Just run the cron hourly job, which will delete the 11th record.

Answer 3

, , , . >= 10, . .

Answer 4

, , , N (), :

• .
• trigger .

, , .

Alternatively, if your goal is to periodically delete old data, then using the cron job to run the stored procedure to trim the old data will make the most sense.

Answer 5

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 6

When you insert a new entry for the user. Just run this query before (don't forget where-condition):

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

After that you can insert new data. This always saves data for ten records for each user.

INSERT INTO tablename VALUES(....)